Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.

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
RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

MayPowerBICarousel

Fabric Monthly Update - May 2024

Check out the May 2024 Fabric update to learn about new features.

LearnSurvey

Fabric certifications survey

Certification feedback opportunity for the community.