Forum Discussion
bzjens
6 years agoFrequent Visitor
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...
- 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
v-lili6-msft
6 years agoCommunity Support
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
6 years agoFrequent Visitor
Nice! Thanks! This pointed me in the right direction.