Conditional Formatting Matrix with Calculation Groups and Date Hierarchy

[triozi]

Hello, 

Requirement is that we have a Matrix table that shows distinct count of activity each week and then have a total cost of that activity as a total at the end.
Simple matrix would put the two values on a separate row. , I did work out how not to display the values other than the total so it's close, but takes up double the rows just to show it.

but I needed it like this: Hello Calculation Groups!

Calculation Groups worked a treat to do this after watching a video on how to have two totals columns of different values in a Matrix,

two Measures
Distinct Weeks:

Distinct Weeks = 
CALCULATE(
    DISTINCTCOUNT('Week Definition'[Start Date (w/c Definition)]),
    FILTER(
        'fact Table',
        'Fact Table'[a1a_startdate] <= MAX('Week Definition'[Start Date (w/c Definition)]) &&
        'Fact Table'[a1a_enddate] >= MIN('Week Definition'[Start Date (w/c Definition)])
    )
)

Dont worry about what week definition means it cold just be start date.

TCC measure that will only Show Total. 

TCC Total = 
IF (
    ISINSCOPE('Week Definition'[Fiscal Year]) ||
    ISINSCOPE('Week Definition'[Fiscal Quarter]) ||
    ISINSCOPE('Week Definition'[Fiscal Month]) ||
    ISINSCOPE('Week Definition'[Start Date (w/c Definition)]),
    BLANK(),
    SUM('Fact Table'[TCC Amount])
)

The weird thing was the instructions was to put a Dummy Measure in values to display the data. Dummy = 1. I did that, it works! BUT... Now I can't seem to crack conditionally formatting the rows. Each row is a Type, and the is a Colour Column Hex code on the Fact Table, to determine the colour of that type for that week of activity. And I have tried so many different ways. 

When I add in the Field Value to the Cell Formatting of the Dummy value ( as that is the only attribute I can formatting too) it just returns white. I’ve checked out many blogs and posts on conditionally formatting matrix visuals when using calculation groups but none of them seem to work. And I can't find anything on if the Date Hierarchy is the thing that is interfering. 
Here's the layour of the data in the Matrix builder 

Any suggestions please? I will be eternally grateful if I can crack this,

[Anonymous]

Hi @triozi ,

It looks like you are trying to use conditional formatting with calculated groups, but it's not working, right?
In our actual test environment , the conditional format is used normally, we try to reproduce your data, but seem to be unable to completely reproduce, can you provide the relevant pbix file or sample data and your expected output results, so that we can better understand your problem and help you solve this problem, look forward to your reply!

[triozi · Accepted solution]

I finally managed to crack a complex Power BI setup with calculation groups, conditional formatting, and dynamic measures. .

Problem:

I needed to create a matrix that dynamically shows:

1. Distinct Weeks of activity for mapping categories.

2. TCC Totals for summary levels Costs, ensuring these don’t clutter the table at granular levels.

3. Conditional formatting based on a MappingColumn using predefined colors from a RowColour field.

Solution:

The setup combines calculation groups, explicit measures, and dynamic conditional formatting into one cohesive solution. Here’s how I solved it:

Key Steps:

1. Using Explicit Measures in Values:

Instead of using a dummy measure, I placed my Distinct Weeks measure directly into the Values section of the matrix. This ensures the measure dynamically calculates for each row/column intersection without any unnecessary placeholders.

Distinct Weeks =
CALCULATE(
    DISTINCTCOUNT('Week Definition'[Start Date (w/c Definition)]),
    FILTER(
        'Fact Table',
        'Fact Table'[StartDate] <= MAX('Week Definition'[Start Date (w/c Definition)]) &&
        'Fact Table'[EndDate] >= MIN('Week Definition'[Start Date (w/c Definition)])
    )
)

2. Calculation Group Items:

• Distinct Weeks Item: This uses SELECTEDMEASURE() directly, ensuring the calculation dynamically adjusts to whatever measure is selected:

• Distinct Weeks = SELECTEDMEASURE()

• TCC Total Item: I scoped this to display totals only at higher levels of the hierarchy (like Fiscal Year or Fiscal Quarter) using ISINSCOPE. This hides it at granular levels:

TCC Total =
IF (
    NOT ISINSCOPE('Week Definition'[Fiscal Year]) &&
    NOT ISINSCOPE('Week Definition'[Fiscal Quarter]) &&
    NOT ISINSCOPE('Week Definition'[Fiscal Month]) &&
    NOT ISINSCOPE('Week Definition'[Start Date (w/c Definition)]),
    SUM('Fact Table'[TCC Amount]),
    BLANK()
)

3. Conditional Formatting:

This was the tricky part. I created a separate measure to apply conditional formatting. This measure checks whether the row corresponds to a calculation group item (Distinct Weeks or TCC Total) or a MappingColumn row and formats accordingly:

RowColourMeasure_SelectedMeasure =
IF (
    SELECTEDMEASURE() IN {"Distinct Weeks", "TCC Total"},
    "#FFFFFF", // Default white for calculation group rows
    LOOKUPVALUE(
        'Fact Table'[RowColour],
        'Fact Table'[MappingColumn], SELECTEDVALUE('Fact Table'[MappingColumn]),
        "#FFFFFF" // Default white
    )
)

4. Matrix Layout:

• Rows: Client Type > Name Column > MappingColumn.

• Columns: Calculation Group Name > Fiscal Hierarchy (Year > Quarter > Month > Week Start Date).

• Values: [Distinct Weeks].

Next Steps:

The next enhancement is to incorporate a field parameter for Totals. This would allow the client to switch dynamically between different measures (e.g., Gross, Net, Total Cost to Client, TCC + TAX) using a slicer. The field parameter will replace the hardcoded calculation group items for these totals and give users more flexibility.

Final Result:

The matrix now dynamically displays the required measures while conditionally formatting the rows based on the mapping category's color. Calculation group rows (like Distinct Weeks and TCC Total) default to white, while mapping category rows show their respective colors. It’s clean, dynamic, and highly functional.

Key Takeaways:

• Use explicit measures in the Values section of the matrix to simplify your setup.

• Leverage SELECTEDMEASURE() in calculation groups to dynamically use existing logic.

• For conditional formatting, also must use  SELECTEDMEASURE() as a separate measure combining LOOKUPVALUE and mapping category logic

• Contextual scoping with ISINSCOPE ensures your totals only show at the right levels of the  "second table" Total from the calculation 

• Field parameters will be a great way to give users more control and flexibility in switching between different metrics.

This approach really unlocked a powerful reporting structure for me. Hopefully, it helps others too! Let me know if you have questions or need further clarification