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

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

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
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

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

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors