Forum Discussion

FM-Rad10's avatar
FM-Rad10
Frequent Visitor
1 year ago
Solved

% Calculation within Group.

  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% ...
Need Help
  • FM-Rad10's avatar
    FM-Rad10
    1 year ago

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

     

    DIVIDE(
    [Value],
    CALCULATE(
        [Value],
        ALLSELECTED('Table'),
        VALUES('Table'[Model])
    )
)
Kimtran's avatar
Kimtran
Regular Visitor
1 year ago

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.