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.
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!
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
)