cancel
Showing results for
Did you mean:

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

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.

Community Support

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.

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.

Announcements

#### Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

#### The Power BI Community Show

Welcome to the Power BI Community Show! Jeroen ter Heerdt talks about the importance of Data Modeling.

#### Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!

#### Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

Top Solution Authors
Top Kudoed Authors