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