Decomposition Tree - Date Hierarchy where sum of percentages of any given level =100%
I have a dataset of 5 years' worth of daily recordings. Each record is simple and has 3 columns:
(Some orders can have two different types. They simply have multiple records, one for each order type).
I need to count the order types and show percentages based on hierarchies and show it on a single decomposition tree so that the user can decide how they wish to look at the data. I am interested in every possible combination using the Total, Year, Quarter, and Order Type:
Number of orders for each year as a percentage of all statuses recorded
Number of orders for each quarter as a percentage of its year
Number of orders for each quarter as a percentage of all statuses
Number of orders for each status type as a percentage of the quarter
Number of orders for each status type as a percentage of the year
Number of orders for each status type as a percentage of all statuses recorded
And I want to be able to fully expand the tree to 4 levels in any particular order (Total-Year-Quarter-Order Type) or only choose 3 or 2 levels.
Is this possible using the date hierarchy off of a calendar table and a Count of the status types?
When I use something like "Percent of TOTAL ORDERS = DISTINCTCOUNT('TABLE1'[Order#])/CALCULATE(DISTINCTCOUNT('TABLE1'[Order#]),ALLEXCEPT('Calendar','Calendar'[Date]))