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