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:
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.