Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Dynamic measure calculation for hierarchy data

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

Dynamic measure calculation for hierarchy data

12-28-2018
07:33 AM

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

Solved! Go to Solution.

2 ACCEPTED SOLUTIONS

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-30-2018
10:04 PM

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

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-02-2019
07:10 PM

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.

If this post

Message 4 of 4

2,343 Views

3 REPLIES 3

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

12-30-2018
10:04 PM

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

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.

If this post

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-02-2019
04:23 AM

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

Message 3 of 4

2,351 Views

- Mark as New
- Bookmark
- Subscribe
- Mute
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

01-02-2019
07:10 PM

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

If this post

Message 4 of 4

2,344 Views

Featured Topics

Top Solution Authors

User | Count |
---|---|

343 | |

156 | |

85 | |

78 | |

44 |

Top Kudoed Authors

User | Count |
---|---|

404 | |

241 | |

112 | |

110 | |

97 |