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

Need Help
  • v-lili6-msft's avatar
    v-lili6-msft
    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

2 Replies

  • v-lili6-msft's avatar
    v-lili6-msft
    Community Support
    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

    • bzjens's avatar
      bzjens
      Frequent Visitor
      6 years ago

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