Slicer to Filter P&L Subtotals

[jason435]

I would like a slicer that changes the P&L subtotals displayed in a chart. Select Revenue, Gross Profit, EBITDA, Net Profit --> update the chart. My tables:

Accounts Table:

Name | SubTotal | Multi | Sub ID

Product Revenue | Revenue | 1 | 1

COGS | Gross Profit | -1 | 2

Marketing | EBITDA | -1 | 3

Depreciations | Net Profit | -1 | 4   etc

Values table:

Month | Name  | Amount

1/1/20 | Product revenue  | $20

1/1/20 | COGS | $5

I already have a measure that seems to work in the context of a table with all the "Subtotals" as rows:

Value P&L PY = 
            CALCULATE(
                        SUMX ( 'Values', 'Values'[Amount] * RELATED ( 'Sub-Totals'[Multi] )
                ),
                FILTER ( ALLSELECTED ( Accounts ), Accounts[Sub ID] <= MIN ( Accounts[Sub ID] ))
            )

This correctly makes a table with:

Revenue | $20

Gross Profit | $15

However, I would like to make a slicer with all the subtotal (revenue, gross profit, EBITDA, net profit, etc). And depending on what I select, it updates the table or chart. The current measure returns Gross Profit = $5

My filter seems to remove all accounts with 'account' <> 'selected slicer account' causing the SUMX not work correctly. Something is wrong with my ALLSELECTED, I just can't figure it out. I have tried so many options. 

I simplified my chart structure significantly here. I have 6 levels of sub-accounts on the P&L, dates, etc. Here I am focusing on these sub-totals that require 'sumx' the amounts with -1 or +1. I'm using a Zebra BI visual to select the subtotal with a chart on the left to show details.

[v-lionel-msft]

Hi @jason435 ,

Like this? 

Cumulative calculation

Value P&L PY = 
            CALCULATE(
                SUMX ( 'Values', 'Values'[Amount] * RELATED ( 'Sub-Totals'[Multi] )),
                FILTER ( ALL( Accounts ), Accounts[Sub ID] <= MIN ( Accounts[Sub ID] ))
            )

Best regards,
Lionel Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[jason435]

Yes, that seems to work. I could have sworn I had tried that. 😤

Thank you so much. 

[Greg_Deckler · Accepted solution]

Not sure I am following this. 

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Maybe MM3TR? https://community.powerbi.com/t5/Quick-Measures-Gallery/Matrix-Measure-Total-Triple-Threat-Rock-amp-...