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 I

Calculate % Sales metric on a drill down table

Please see the table I am trying to achieve below :

I have a drill down table with the 1st level of the table containing the 'Event type' sales (Event Rental & Additional highlighted in blue) and the table further drills down to the ticket level sales (Rental A,B etc). I am trying to get the % sales and that calculation gets tricky in a drill down table as -

1. The % sales at the 1st level of table containing the event type sales (in blue) is calculated as Event type sales/Total sales

2. The % sales at the 2nd level of table containing the ticket types is calculated as sales at each ticket level/Total Event type revenue (pls see the formula on the screenshot)

What I have been able to achieve on PowerBI so far:

I have used the formula below-

%  Contribution =
DIVIDE(
SUM('AGGREGATE_REVENUES (2)'[Sales),
CALCULATE(SUM('AGGREGATE_REVENUES (2)'[Sales]), ALL('AGGREGATE_REVENUES (2)'[Event Type], 'AGGREGATE_REVENUES (2)'[Ticket Type])
))

This formula gives me the correct % for the 1st level of the table but when I drill down to the ticket level sales, the % is incorrect. I have not been succesful in finding any other formulas on the internet. Any help will be greatly appreciated.

Thank you!
1 ACCEPTED SOLUTION
Community Support

Based on your description, you can create a measure like this to calculate the percentage:

``````% Sales =
IF (
ISINSCOPE ( 'Table'[Category] ),
SUM ( 'Table'[Revenue] )
/ CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( ALL ( 'Table' ), 'Table'[Class] IN DISTINCT ( 'Table'[Class] ) )
),
CALCULATE ( SUM ( 'Table'[Revenue] ), ALLEXCEPT ( 'Table', 'Table'[Class] ) )
/ CALCULATE ( SUM ( 'Table'[Revenue] ), ALL ( 'Table' ) )
)``````

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

3 REPLIES 3
Community Support

Based on your description, you can create a measure like this to calculate the percentage:

``````% Sales =
IF (
ISINSCOPE ( 'Table'[Category] ),
SUM ( 'Table'[Revenue] )
/ CALCULATE (
SUM ( 'Table'[Revenue] ),
FILTER ( ALL ( 'Table' ), 'Table'[Class] IN DISTINCT ( 'Table'[Class] ) )
),
CALCULATE ( SUM ( 'Table'[Revenue] ), ALLEXCEPT ( 'Table', 'Table'[Class] ) )
/ CALCULATE ( SUM ( 'Table'[Revenue] ), ALL ( 'Table' ) )
)``````

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Continued Contributor

What happens if, in your dax code, you change [Ticket Type] into [Ticket Level] (or whatever you use for "Rental A" and such)?

Helper I

I am getting a 100% across all the ticket type sales (Rental A etc)

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.

Fabric Community Update - August 2024

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

Top Solution Authors
Top Kudoed Authors