Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
mariaschmieder
Frequent Visitor

How to change the totals for each level in decomposition tree

Hello! I have a question about the decomposition tree visual. Based on the nature of this visual, I am wondering if it actually would even have the capability to do this, and if anyone knows. I am going to use an example, because I think that will be the easiest way to explain it.

 

For the 'analyze by' field I am using the column called "contact hours" and it is a sum of these values. Then for the decomposition tree the first "explain by" level is called Phase. There are 5 phases... then the next level is Step - there are 3 steps per Phase. The last level is Milestone. One person may have contact hours for 3 milestones that are under one Step. When it is at the milestone level I want it to sum the hours overall for each milestone. But when I get to the step phase, I only want the hours summed one time for that person, rather than summing it 3 times because the contact hours show up 3 times - one for each milestone (note - each person's contact hours will be the same value for each milestone, step, and value)... so trying to avoid duplication of each person's contact hours depending on the level...

 

So if a person had 5 contact hours for 2 milestones under step 1 (phase 1) and 1 milestone under step 2 (phase 1), then the contact hours for milestones would be 15 hours, the step would be 10 contact hours (included only once for each step) and then 5 hours for phase (counted one time for that phase). So for Step I want it to sum the hours for each person only one time, regardless how many milestones they have contact hours for under that step. I want to do the same thing for phase - I only want it to sum contact hours one time for each person in that phase, regaardless of how many milestones and steps they are under... so if one person has many milestones under many different steps in that phase, I want it to only appear once in the sum.

 

Is there any way to achieve this, knowing that there is only one field for the Analyze by? Like I said... based on the nature of the decomposition tree it seems that maybe this would not be possible?.....Any advice is helpful, thanks in advance! (apoogies if this was already asked... I looked through and could not find this)

1 ACCEPTED SOLUTION

Hello again,

 

I figured it out! Here is the final formula I used in the measure:

 

Sum Hours Percentage =
VAR TotalContactHours = 23197
VAR IsPhaseLevel = ISFILTERED('Milestones (2)'[Phase]) && NOT ISFILTERED('Milestones (2)'[Step])
VAR IsStepLevel = ISFILTERED('Milestones (2)'[Step]) && NOT ISFILTERED('Milestones (2)'[Milestone Number])
VAR UniquePersonPhase = SUMMARIZE('Milestones', 'Milestones'[Service Contact Id], 'Milestones (2)'[Phase], "Hours", MAX('Milestones'[Contact Hours]))
VAR UniquePersonStep = SUMMARIZE('Milestones', 'Milestones'[Service Contact Id], 'Milestones'[Phase&Step with ID], "Hours", MAX('Milestones'[Contact Hours]))
VAR HoursSum =
IF(
    IsPhaseLevel,
    SUMX(UniquePersonPhase, [Hours]),
IF(
    IsStepLevel,
    SUMX(UniquePersonStep, [Hours]),
    SUM('Milestones'[Contact Hours])
)
)
RETURN
Divide(HoursSum, TotalContactHours)

View solution in original post

3 REPLIES 3
Daniel29195
Super User
Super User

@mariaschmieder 

you can check the calculation in a decomposition tree base on the level you are on : 

check example below : 

Daniel29195_0-1708729742140.png

 

 

in able to do this, you can use ISFILTERED()  or ISINSCOPE() functions .

 

let me know if this helps .

 

 

If my answer helped sort things out for you, i would appreciate a thumbs up 👍 and mark it as the solution
It makes a difference and might help someone else too. Thanks for spreading the good vibes! 🤠

Hello, thank you for this... I realize I definitely labeled my subject line wrong... so the reason I said it is different totals is becuase I want to make sure that one person's hours are only being counted once when I go to the Step level in the decomposition tree. So, one person may have 3 hours for 5 different milestones.... so this gets summed under each milestone, but when it gets to the Step level, I only want that person's hours summed one time for that Step, so the person's hours are not duplicated in the Step level.

 

Decomposition tree.png

 

Hello again,

 

I figured it out! Here is the final formula I used in the measure:

 

Sum Hours Percentage =
VAR TotalContactHours = 23197
VAR IsPhaseLevel = ISFILTERED('Milestones (2)'[Phase]) && NOT ISFILTERED('Milestones (2)'[Step])
VAR IsStepLevel = ISFILTERED('Milestones (2)'[Step]) && NOT ISFILTERED('Milestones (2)'[Milestone Number])
VAR UniquePersonPhase = SUMMARIZE('Milestones', 'Milestones'[Service Contact Id], 'Milestones (2)'[Phase], "Hours", MAX('Milestones'[Contact Hours]))
VAR UniquePersonStep = SUMMARIZE('Milestones', 'Milestones'[Service Contact Id], 'Milestones'[Phase&Step with ID], "Hours", MAX('Milestones'[Contact Hours]))
VAR HoursSum =
IF(
    IsPhaseLevel,
    SUMX(UniquePersonPhase, [Hours]),
IF(
    IsStepLevel,
    SUMX(UniquePersonStep, [Hours]),
    SUM('Milestones'[Contact Hours])
)
)
RETURN
Divide(HoursSum, TotalContactHours)

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

Find out what's new and trending in the Fabric Community.

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

Check out the November 2024 Power BI update to learn about new features.