Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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)
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
108 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |