cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
saurabh_kedia_ Regular Visitor
Regular Visitor

Dynamic measure calculation for hierarchy data

Hi Folks,

 

I'm trying to calculate one measure which can display different values depending on the level of drill down.

 

 

 

Below is the hierarchy:

Level1 Name	Level2 Name
A	C
B	D
B	E
B	F
B	G

Line item data for specific person is mentioned below at different levels

 

Level 1 :

 

Name	Level1 Name	Completed	Inprogress	Not_Started
ACH	A	1		
ERN	A	1		
EEB	A			1
EKS	A			1
BAO	A			1
EES	A		1	
CHE	A		1	
URK	B	1		
RIE	B	1		
ORS	B	1		
CAT	B			1
HJO	B			1
NMC	B		1	
ULY	B			1
WIL	B		1	

Level2:

 

Name	Level2 Name	Completed	Inprogress	Not_Started
EEB	C			1
EKS	C			1
CAT	D			1
CAT	E			1
CAT	F			1
CAT	G			1
HJO	D			1
HJO	E			1
HJO	F			1
HJO	G			1
NMC	D		1	
NMC	F		1	
NMC	G		1	
NMC	E			1
BAO	C			1
ULY	D			1
ULY	E			1
ULY	F			1
ULY	G			1
EES	C			1
WIL	D	1		
WIL	E			1
WIL	F			1
WIL	G			1
CHE	C	1		
ACH	C	1		
URK	F		1	
URK	G		1	
URK	D	1		
URK	E	1		
RIE	F		1	
RIE	G		1	
RIE	D	1		
RIE	E	1		
ERN	C	1		
ORS	D	1		
ORS	E	1		
ORS	F			1
ORS	G			1

Summarize level1:

Level1 Name	Sum of Not_Started	Sum of Inprogress	Sum of Completed
A	3	2	2
B	3	2	3
Grand Total	6	4	5

Summarize level2:

Level2 Name	Sum of Not_Started	Sum of Inprogress	Sum of Completed
C	4	0	3
D	3	1	4
E	5	0	3
F	5	3	0
G	5	3	0
Grand Total	22	7	10

Now the main issue is the data exactly doesn't add up from one hierarchy to another.

Currently I;m displaying to different outputs i.e. one at Level1 & other at Level2.

Although I need one measure for each calculation i.e. Not_Started, Inprogress & Completed.

 

Required Output:

 

Level1 Name	Sum of Not_Started	Sum of Inprogress	Sum of Completed
A	3	2	2
C	4	0	3
B	3	2	3
D	3	1	4
E	5	0	3
F	5	3	0
G	5	3	0
Grand Total	6	4	5

The grand total for completed is 5 i.e. A is 2 & B is 3.

Although the values of the next level respectively doesn't add up to the same i.e. Level3 ; D=4,E=3 doesn't add up to B's total amount i.e. 3

 

Basically I'm trying to achieve this in a matrix visual.

 

Please feel free to contact for more info if required.

 

Regards,

Saurabh

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-jiascu-msft Super Contributor
Super Contributor

Re: Dynamic measure calculation for hierarchy data

Hi Saurabh,

 

Please download the demo from the attachment then you can check the details.

1. Establish proper relationships.

2. Create three measures.

Not_Started Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Not_Started] ),
    SUM ( Table2[Not_Started] )
)
Inprogress Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Inprogress] ),
    SUM ( Table2[Inprogress] )
)
Completed Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Completed] ),
    SUM ( Table2[Completed] )
)

Dynamic-measure-calculation-for-hierarchy-data

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
v-jiascu-msft Super Contributor
Super Contributor

Re: Dynamic measure calculation for hierarchy data

Hi Saurabh,

 

Did it have the similar structure for the third level like below?

Level1 Name    Level2 Name     Level3 Name

 

If so, please try this formula. If not, please provide a sample. The main idea is recognizing the lower level first.

 

Completed Sum =
IF (
    ISFILTERED ( Table2_1[Level1 Name] ),
    SUM ( Table2_1[Completed] ),
    IF (
        ISFILTERED ( Table1[Level2 Name] ),
        SUM ( Table3[Completed] ),
        SUM ( Table2[Completed] )
    )
)

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
3 REPLIES 3
v-jiascu-msft Super Contributor
Super Contributor

Re: Dynamic measure calculation for hierarchy data

Hi Saurabh,

 

Please download the demo from the attachment then you can check the details.

1. Establish proper relationships.

2. Create three measures.

Not_Started Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Not_Started] ),
    SUM ( Table2[Not_Started] )
)
Inprogress Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Inprogress] ),
    SUM ( Table2[Inprogress] )
)
Completed Sum =
IF (
    ISFILTERED ( Table1[Level2 Name] ),
    SUM ( Table3[Completed] ),
    SUM ( Table2[Completed] )
)

Dynamic-measure-calculation-for-hierarchy-data

 

Best Regards,
Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
saurabh_kedia_ Regular Visitor
Regular Visitor

Re: Dynamic measure calculation for hierarchy data

Hi Jiascu,

 

Thanks a lot for your response.

It is working well with two levels of hierarchy but I actually needed the same for three levels of hierarchy.

 

I thought the solution can simply be implemented on to three levels although this was not the case.

Assuming there is one more table in the hierarchy i.e. above Table 2 namely Table2_1, how can the same be implmented.

I tried to make a calculation as :

 

Completed Sum =

 

IF(ISFILTERED(Table2_1[Level1 Name])=FALSE, SUM(Table2_1[Completed]),IF(ISFILTERED(Table1)=FALSE,SUM(Table1[Completed]),SUM(Table2[Completed]))

 

Kindly assist on the same, I have searched through some other articles but this is apprently not so straight forward I guess.

 

Regards,

Saurabh

v-jiascu-msft Super Contributor
Super Contributor

Re: Dynamic measure calculation for hierarchy data

Hi Saurabh,

 

Did it have the similar structure for the third level like below?

Level1 Name    Level2 Name     Level3 Name

 

If so, please try this formula. If not, please provide a sample. The main idea is recognizing the lower level first.

 

Completed Sum =
IF (
    ISFILTERED ( Table2_1[Level1 Name] ),
    SUM ( Table2_1[Completed] ),
    IF (
        ISFILTERED ( Table1[Level2 Name] ),
        SUM ( Table3[Completed] ),
        SUM ( Table2[Completed] )
    )
)

 

Best Regards,
Dale

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

Helpful resources

Announcements
Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Summit Australia 2019

Summit Australia 2019

Travel to Melbourne and network with thousands of peers!

Top Ideas
Users Online
Currently online: 274 members 2,919 guests
Please welcome our newest community members: