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

Get certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now

Reply
Chrisjr
Helper IV
Helper IV

Average of average measure

Hello All, 

 

 

I have the below table

N GroupMatricule DealerWavesum scoreAVG all waves 
500ER102214044,50 
500ER102224944,50 
500ER170015656 
500ER17002 56 
200ER108016464 
200ER10802 64 

 

 

The mesure sum score is : SUM(Fact_AuditMasterfile[SCORE])

 

The measure AVG all waves is : CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            Fact_AuditMasterfile,
            Lookup_Push[Wave],
            Lookup_Dealer[Matricule Dealer],
            Lookup_Dealer[N°Group],
            "avg"SUM ( Fact_AuditMasterfile[SCORE] )
        ),
        [avg]
    ),
    ALLEXCEPT ( Fact_AuditMasterfile, Lookup_Dealer[Matricule Dealer] )
)

 

 

 

What I am trying to achieve is now to get the average of the AVG all waves measure. per N group

 

The end result would be as below :

N GroupMatricule DealerWavesum scoreAVG all wavesAVG of AVG all waves
500ER102214044,5050,25
500ER102224944,50 50,25
500ER170015656 50,25
500ER17002 56 50,25
200ER10801646464
200ER10802 64

64

 

So for N Group 500, it woul be the average of 44.50+56

 

How could I achive this ? 

 

Thanks

1 ACCEPTED SOLUTION
lbendlin
Super User
Super User

You cannot measure a measure. Instead, implement your desired business logic from scratch

 

lbendlin_0-1707355188984.png

 

 

 

Avg all waves = 
CALCULATE(average('Table'[sum score]),REMOVEFILTERS('Table'[Wave]))

Avg of Avg of waves = 
var a = SUMMARIZE(allselected('Table'),[N Group],[Matricule Dealer],"s",[Avg all waves])
var b = SELECTEDVALUE('Table'[N Group])
RETURN averagex(filter(a,[N Group]=b),[s])

 

Note: I do not like the way the blank values are ignored for the average, but that's probably just me.

 

View solution in original post

3 REPLIES 3
Chrisjr
Helper IV
Helper IV

many thanks @lbendlin 

lbendlin
Super User
Super User

You cannot measure a measure. Instead, implement your desired business logic from scratch

 

lbendlin_0-1707355188984.png

 

 

 

Avg all waves = 
CALCULATE(average('Table'[sum score]),REMOVEFILTERS('Table'[Wave]))

Avg of Avg of waves = 
var a = SUMMARIZE(allselected('Table'),[N Group],[Matricule Dealer],"s",[Avg all waves])
var b = SELECTEDVALUE('Table'[N Group])
RETURN averagex(filter(a,[N Group]=b),[s])

 

Note: I do not like the way the blank values are ignored for the average, but that's probably just me.

 

for the blank values, that's okay as they will be filled in later, so blank values should be ignored

Helpful resources

Announcements
November Carousel

Fabric Community Update - November 2024

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

Live Sessions with Fabric DB

Be one of the first to start using Fabric Databases

Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.

Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.

Nov PBI Update Carousel

Power BI Monthly Update - November 2024

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