% Calculation within Group.

[FM-Rad10]

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. 

[FM-Rad10 · Accepted solution]

I found the solution after a lot of trial and error. 

DIVIDE(
    [Value],
    CALCULATE(
        [Value],
        ALLSELECTED('Table'),
        VALUES('Table'[Model])
    )
)

[DataNinja777]

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,

[FM-Rad10]

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

[Kimtran]

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.