Dynamic measure calculation for hierarchy data

[saurabh_kedia_]

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

[v-jiascu-msft · Accepted solution]

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] )
)

Best Regards,
Dale

[v-jiascu-msft · Accepted solution]

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] )
)

Best Regards,
Dale

[saurabh_kedia_]

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 · Accepted solution]

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