Measure Total Issue - "True'' average

[Anonymous]

Hi all!

I need your help!

I have this measure:

 Single_Res RpD LY WeekDay (DB) =

VAR

CurrentDate = MAX(RezReporting[Date Booked 2])

RETURN

(CALCULATE([Res RpD], FILTER(ALL('Date Booked'),'Date Booked'[DWY]=MAX('Date Booked'[DWY])-1),RezReporting[Date Booked 2]<CurrentDate))

Like many people, I have the ''total measure issue''.  But instead of a sum, I want the weighted average.

Right now I did something like this below using the HASONEFILTER method:

Res RpD LY WeekDay (DB) =

IF(HASONEFILTER('Date Booked'[DayOfWeek]),

[Single_Res RpD LY WeekDay (DB)],

AVERAGEX('Date Booked',[Single_Res RpD LY WeekDay (DB)]))

But, I need a replacement for the AVERAGEX because I don't want a simple average, but a weighted average. Lets take the following data set as an example:

https://sharingmatters-my.sharepoint.com/:i:/g/personal/merel_vanstraaten_enterprise-netherlands_com...

Please let me know how I can get a weighted average in the total from the measure.

THANKS!

Merel

[v-yulgu-msft]

Hi @Anonymous,

What is the calculation rule for weighted average in your scenario?

Regards,

Yuliana Gu

[Anonymous]

Hi Yuliana,

[Res RpD] = 

divide(sumx(RezReporting,RezReporting[Total Bill Rate Charge]+RezReporting[Total Bill Surcharge Charge]+RezReporting[Total Bill Tax 1 Charge]),[Res Days])

Hope you can help me!

Let me know if you need anything else.

[Anonymous · Accepted solution]

Hi all!

I was hoping to have a more efficient DAX code, but I managed to get the desired results by using the following:

Res RpD LY WeekDay (DB) =

    IF(

        HASONEFILTER('Date Booked'[Date Booked]),

        [Single_Res RpD LY WeekDay (DB)],

        DIVIDE(

            SUMX(

                'Date Booked',

                [Res Rate+Surch+VLF LY Weekday]),

                SUMX(

                     'Date Booked',

                    [Res Days LY WeekDay (DB)])))

So this case is closed for now.

Thanks!

Merel