Forum Discussion

bzjens's avatar
bzjens
Frequent Visitor
6 years ago
Solved

P&L statement calculated row in matrix visual

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 calcu...
  • v-lili6-msft's avatar
    6 years ago

    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