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])
    )
)
DataNinja777's avatar
DataNinja777
Super User
1 year ago

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's avatar
    FM-Rad10
    Frequent Visitor
    1 year ago

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