Forum Discussion

flushedpeach's avatar
flushedpeach
Regular Visitor
6 months ago
Solved

Creating Unique Subtotal for Power BI Matrix Visualization but Some Rows not Showing Value - Help!

I have a matrix visualization with two dimension fields, Category and Metric. For each Category, there can be different Metrics (or subcategory). I am attempting to calculate custom subtotal values t...
Need Help
  • flushedpeach's avatar
    flushedpeach
    6 months ago

    Amar_Kumar Thank you! I experimented a little bit and while that formula didn't quite work, this one seemed to do the trick:

    SWITCH(
        TRUE(),
        ISINSCOPE(UnionTable[Metric]),
            [Current Month],
        ISINSCOPE(UnionTable[Category]) && NOT ISINSCOPE(UnionTable[Metric]),
            CALCULATE(
                [Monthly_Actual to Number Measure],
                REMOVEFILTERS(UnionTable[Metric])
            ),
        BLANK()
    )

    I hope the REMOVEFILTERS won't cause any issues.
Amar_Kumar's avatar
Amar_Kumar
Super User
6 months ago

This is a filter / relationship issue, not an ISINSCOPE issue.

Your matrix uses Category and Metric from UnionTable.
At the Category subtotal level, the filter on UnionTable[Category] does not propagate to bsc_outcome, so [Monthly_Actual to Number Measure] evaluates to blank. That’s why measures from UnionTable work but the one from bsc_outcome does not.

Quick fix is to force the Category filter onto bsc_outcome:

Matrix Value for Category Test v2 =
SWITCH(
    TRUE(),
    ISINSCOPE(UnionTable[Metric]),
        [Current Month],
    ISINSCOPE(UnionTable[Category]),
        CALCULATE(
            [Monthly_Actual to Number Measure],
            TREATAS(
                VALUES(UnionTable[Category]),
                bsc_outcome[Category]
            )
        ),
    BLANK()
)

Root cause is either:

  • No active relationship between UnionTable and bsc_outcome

  • Category does not filter bsc_outcome

  • Relationship direction is wrong

Fixing the model relationship can also solve it without TREATAS.