P&L statement calculated row in matrix visual

[bzjens]

Hi!

I am trying to add a calculated row to a profit & loss statement matrix visual

(very simplified in this post)

This is what I have:

This is what I want:

Here Tax is calculated as 25% of "Total before tax"

And these are my tables and relations:

Does this has an easy solution? (one that I don't see....)

Thanks!

Børre

[v-lili6-msft · Accepted solution]

hi, @bzjens 

For your requirement, you could try this way as below:

Step1:

Add a row for Tax in Accounts table as below:

Step2:

Add a measure by this logic

Measure = 
VAR _table =
    SUMMARIZE (
        Accounts,
        Accounts[category],
        Accounts[subcategory],
        "a", IF (
            SELECTEDVALUE ( Accounts[category] ) = "Total before Tax",
            CALCULATE ( SUM ( Transactions[Value] ), ALLSELECTED ( Accounts ) ) * 0.25,
            CALCULATE ( SUM ( Transactions[Value] ) )
        )
    )
RETURN
    IF (
        HASONEVALUE ( Accounts[category] ),
        IF (
            SELECTEDVALUE ( Accounts[category] ) = "Total before Tax",
            CALCULATE ( SUM ( Transactions[Value] ), ALLSELECTED ( Accounts ) ) * 0.25,
            CALCULATE ( SUM ( Transactions[Value] ) )
        ),
        SUMX ( _table, [a] )
    )

Then use this measure in matrix visual.

Result:

and here is sample pbix file, please try it.

Regards,

Lin

[bzjens]

Nice! Thanks! This pointed me in the right direction.