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

[flushedpeach]

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 to display on rows in matrix, but having issues in returning a value at the Category level. For this special Category row value, "Monthly_Actual to Number Measure", won't return for some reason. I will say that it is a measure from a different table ('bsc_outcome') whereas the other measure is coming from the 'Union_Table'. I replaced this value with a different value from 'Uniton_Table' to test and it worked, so it has something to do with table relationship or filters? Not sure. See image and dax calculation below:





Matrix Value for Category Test v2 =

SWITCH (

    TRUE(),

    ISINSCOPE ( UnionTable[Metric] ),

        [Current Month],

    ISINSCOPE ( UnionTable[Category] ),

            [Monthly_Actual to Number Measure],

    BLANK()

)

Thank you!!!

[flushedpeach · Accepted solution]

@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]

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.