cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Helper II

## Slicer to Filter P&L Subtotals

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.

1 ACCEPTED SOLUTION
Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...
3 REPLIES 3
Community Support

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.

Helper II

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

Thank you so much.

Super User

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

Become an expert!: Enterprise DNA
External Tools: MSHGQM
Latest book!:
Power BI Cookbook Third Edition (Color)

DAX is easy, CALCULATE makes DAX hard...

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

#### Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors