cancel
Showing results for
Did you mean:
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.

Resolver III

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