The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredCompete to become Power BI Data Viz World Champion! First round ends August 18th. Get started.
I have the following Matrix
Colour | Model | Count of Key | % |
Blue | XX1 | 3407 | 81.31% |
Blue | XX2 | 38856 | 72.33% |
Red | XX1 | 472 | 11.26% |
Red | XX2 | 9570 | 17.81% |
Green | XX1 | 311 | 7.42% |
Green | XX2 | 5298 | 9.86% |
The % calculation is done with the Measure below so I can see what % of each Model the count is.
Key % = DIVIDE( DISTINCTCOUNT(SQL[Key]) , CALCULATE(DISTINCTCOUNT(SQL[Key]), ALLEXCEPT(SQL,SQL[Model]) ) )
My issue is that if I were to filter the Colour to only show Blue and Red, the % calculation would still show the % for all 3 colours and not recalculate it to address the filtered data.
Like this:
Colour | Model | Count of Key | % |
Blue | XX1 | 3407 | 81.31% |
Blue | XX2 | 38856 | 72.33% |
Red | XX1 | 472 | 11.26% |
Red | XX2 | 9570 | 17.81% |
My report has many Matrices like this so I need the % measure to be as flexible as possible as we're always looking at the Models % but against different attributes.
Does anyone have any idea how to get it to recalcuate when filtered and also mean I dont' need an individual measure for each matrix.
Solved! Go to Solution.
I found the solution after a lot of trial and error.
DIVIDE(
[Value],
CALCULATE(
[Value],
ALLSELECTED('Table'),
VALUES('Table'[Model])
)
)
I found the solution after a lot of trial and error.
DIVIDE(
[Value],
CALCULATE(
[Value],
ALLSELECTED('Table'),
VALUES('Table'[Model])
)
)
Hi @FM-Rad10 ,
The issue comes from using ALLEXCEPT(SQL, SQL[Model]), which strips out all filters except on the Model column. That means even when you apply a filter on Colour, it has no effect on your percentage calculation because the denominator is still calculated over all colours. To fix this and make the measure respond dynamically to filters, you can replace ALLEXCEPT with either REMOVEFILTERS(SQL[Colour]) or, more flexibly, use ALLSELECTED to maintain slicers and visual context.
Here's a modified version using REMOVEFILTERS, which recalculates the percentage within the filtered Colour group:
Key % =
DIVIDE(
DISTINCTCOUNT(SQL[Key]),
CALCULATE(
DISTINCTCOUNT(SQL[Key]),
REMOVEFILTERS(SQL[Colour])
)
)
Alternatively, if your report has multiple matrices with different filters and you want the measure to flex based on what's selected in the visual or slicer, use ALLSELECTED like this
Key % =
DIVIDE(
DISTINCTCOUNT(SQL[Key]),
CALCULATE(
DISTINCTCOUNT(SQL[Key]),
ALLSELECTED(SQL[Colour], SQL[Model])
)
)
This way, the denominator adjusts according to the current filters in the report while still showing the percentage within the visible group, avoiding the need to create custom measures for each matrix.
Best regards,
Thanks for the reply. I may not have explained my issue correctly.
I need the total of the calculated % to always equal 100% for each Model, regardless of what other attributes are in the matrix or what slicers are applied. They currently do this when no slicers are used if I use ALLEXCEPT, but when a slicer is applied they don't total 100% for each Model.
Applying your suggestions above I get this: (I've also reformatted the table to make the grouping easier to understand.)
Try this measure:
Key % = DIVIDE(
DISTINCTCOUNT(SQL[Key]),
CALCULATE( DISTINCTCOUNT(SQL[Key]),
REMOVEFILTERS(SQL[Colour]) ) )
REMOVEFILTERS(SQL[Colour]) ensures the denominator ignores the Colour filter so that the % recalculates within the currently visible filter context, such as Model or Page/Visual filters. This formula ensures that when you filter Colour, the % updates accordingly.
To build a flexible, reusable measure that works across different matrices for analyzing percentages by various attributes (e.g., Region, Brand), use this pattern:
Key % = DIVIDE( #
DISTINCTCOUNT(SQL[Key]),
CALCULATE( DISTINCTCOUNT(SQL[Key]),
REMOVEFILTERS(SQL[YourGroupingColumn]) ) )
Here, YourGroupingColumn is the axis of interest (e.g., Model, Region) — the level at which you're grouping your rows. For even more flexibility, you can use dynamic DAX with ISINSCOPE() to automatically detect the context level.