Forum Discussion

flushedpeach's avatar
flushedpeach
Regular Visitor
7 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 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!!!

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.

2 Replies

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

  • flushedpeach's avatar
    flushedpeach
    Regular Visitor
    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.