Decomposition Tree - Date Hierarchy where sum of percentages of any given level =100%

[jlankford]

Hello, 

I have a dataset of 5 years' worth of daily recordings. Each record is simple and has 3 columns:

• Date
• Order Number
• Order type

(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]))

It only give me % of the year. 

I'm stumped. 

[v-zhenbw-msft]

Hi @jlankford ,

Maybe you can try this measure,

Percent of TOTAL ORDERS =
DISTINCTCOUNT ( 'TABLE1'[Order#] )
    / CALCULATE (
        DISTINCTCOUNT ( 'TABLE1'[Order#] ),
        FILTER (
            ALLSELECTED ( TABLE1 ),
            TABLE1[Order type] = MAX ( TABLE1[Order type] )
        )
    )

If it doesn’t meet your requirement, could you please provide a mockup sample based on fake data?

It will be helpful if you can show us the exact expected result based on the tables.

Please upload your files to OneDrive For Business and share the link here. Please don't contain any Confidential Information or Real data in your reply.

Best regards,

Community Support Team _ zhenbw

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.