powerbidaxmeasure

PowerBi measure where the total is based on the legend and adjusts total for selected slicer


I have a table in PBI that looks like this:

ID Name Date Performance Country Region
1 Joe 12/31/2023 Good UK Europe
2 Anna 12/31/2023 Needs Improvement Spain Europe
3 Robert 12/31/2023 Good Japan Asia
4 Nate 12/31/2023 Exceeds Expectations UK Europe
5 Emma 12/31/2023 Good Japan Asia
1 Joe 12/31/2024 Outstanding UK Europe
2 Anna 12/31/2024 Good Spain Europe
3 Robert 12/31/2024 Good Japan Asia
4 Nate 12/31/2024 Good UK Europe
5 Emma 12/31/2024 Exceeds Expectations Japan Asia

I'm trying to build a line chart out of it so I can visualize a bell curve of performance percentage (eg. x% good performers, y% needs improvement etc), and compare how the curve changed through the years.

pic for reference

So for X axis I added Performance, and the legend is based on Date.

For Y axis I tried this measure:

Total % = 
DIVIDE(
    COUNT('Data'[Performance]),
    CALCULATE(
        COUNT('Data'[Performance]),
        ALLEXCEPT('Data', 'Data'[Date])
    ),
    0 
)

And this works in the sense that the total is now based on the date, but if I add a slicer based on country or region, I want the total to change as appropriate based on the slicer. So for example, if I filter for UK in the slicer, I want the total to be first year then UK.

But with how things work now, the total is still based only on the date column, so when I select the UK on the slicer, I get very low percentages because 'good' performers might be 10% of the global.

after selecting a slicer, the total is only based on the date, not lowered by the slicer

To reiterate, what I want to achieve though is when I select something else on the slicer, the total should be based on both year and the slicer.

Any way I can achieve that? Many thanks!


Solution

  • ALLEXCEPT is removing filters on all the other columns. ALLSELECTED is commonly used for %. In your case, you also want to maintain the filter context on Date (via VALUES or DISTINCT), so you could do:

    Total % = 
      DIVIDE(
        COUNT('Data'[Performance]),
        CALCULATE(
          COUNT('Data'[Performance]),
          ALLSELECTED('Data'),
          DISTINCT('Data'[Date])
        ),
        0 
      )