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