Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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-
Solved! Go to 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.
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.
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)