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
binayjethwa
Helper IV
Helper IV

Average for Hierarchial Data not working as Expected

Hi , 

I have the following requirement where i need to show performance of the hierarchial data across level 1,2,3,4,5.

So average performance of level 4 = Average of level 5, average performance of level 3 = average of level 4 and so on.

 We have hierarchial data already in our model as below.

 

The issue is , we are averaginf the performance and if you consider below example , as per hierarchy , sum of average performance of level 4 is performance of level 3 . 

 

For below financial sustainability for level 3 overall performance is 54% which should be average of level 4 ( 50%,44%,55% ,74%) but if we calculate average its actually 56%.

I have already tried average by category in quick measure as below which is also not working as expected.

Performance_New average per Item =
AVERAGEX(
    KEEPFILTERS(VALUES('All_Bls_Fct'[Item])),
    CALCULATE(SUM(All_Bls_Fct[Performance_New])
))
 
and also tried below measure.
 
AVERAGEX(VALUES(Table4[Product]),CALCULATE(SUM([Amount])))

Note: Since hierarchty is already available in our model i have just created single measure for Performance and using it across and i am using average option from power bi default option.

 

Kindly help on how to proceed with this issue. Thanks in advance.

 

binayjethwa_0-1673616212309.png

binayjethwa_1-1673616672118.png

 

 

4 REPLIES 4
amitchandak
Super User
Super User

@binayjethwa ,

If you want level 4 to averaged at level 3

 

Performance_New average per Item =
AVERAGEX(
(VALUES('Table'[Level 4]),
CALCULATE(SUM(All_Bls_Fct[Performance_New])
))

Hi @amitchandak This is not working as expected as we have data only for level 5,6 , all these 5 and 6 have to roll up till level 1 , hence cannot separately calculate average for level 4 separately. 

@binayjethwa , Using isinscope measure for each level

 

Switch(True() ,

isinscope(Table[Level 6]) , SUM(All_Bls_Fct[Performance_New]) , //lowest level

isinscope(Table[Level 5]) , AVERAGEX(VALUES('Table'[Level 6]),CALCULATE(SUM(All_Bls_Fct[Performance_New]))),

isinscope(Table[Level 4]) , AVERAGEX(VALUES('Table'[Level 5]),CALCULATE(SUM(All_Bls_Fct[Performance_New]))),

// add others

)

 

Hi @amitchandak

This will be my hierarchial data and i have mapped it to my fact table with level 5,6 which are common across 2 tables.

I can calculate performance only based on values givrn for level 5,6. Hence how do i create measure which will give these averages correctly. Level_mapping is level .

binayjethwa_0-1673620459858.png

 

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

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

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors