How to change the totals for each level in decomposition tree

[mariaschmieder]

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)

[Daniel29195]

@mariaschmieder 

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

check example below : 

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! 🤠

[mariaschmieder]

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.

[mariaschmieder · 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)