cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
aksharacoomar
Helper I
Helper I

Calculate % Sales metric on a drill down table

Please see the table I am trying to achieve below : 

aksharacoomar_0-1611158910194.png

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
v-yingjl
Community Support
Community Support

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

percentage、.png

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.

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

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

percentage、.png

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.

View solution in original post

Gabriel_Walkman
Resolver III
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)?

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

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS Attendee Badge

Claim Your Badge & Digital Swag!

Check out how to claim yours today!

secondImage

Are You Ready?

Test your skills now with the Cloud Skills Challenge.

Top Solution Authors