Calculate % Sales metric on a drill down table

[aksharacoomar]

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! 

[v-yingjl · Accepted solution]

Hi @aksharacoomar ,

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' ) )
)

Attached a sample file in the below, hopes to help you.

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.

[Gabriel_Walkman]

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

[aksharacoomar]

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