Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code FABINSIDER for a $400 discount.
Register nowThe Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.
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)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
Check out the February 2025 Power BI update to learn about new features.
User | Count |
---|---|
85 | |
79 | |
54 | |
39 | |
35 |
User | Count |
---|---|
102 | |
80 | |
48 | |
48 | |
48 |