cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
merelvstr Frequent Visitor
Frequent Visitor

Measure Total Issue - "True'' average

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:
 
 
 
Please let me know how I can get a weighted average in the total from the measure.
 
THANKS!
Merel
 
1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
merelvstr Frequent Visitor
Frequent Visitor

Re: Measure Total Issue - "True'' average

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
 
 
3 REPLIES 3
Community Support Team
Community Support Team

Re: Measure Total Issue - "True'' average

Hi @merelvstr,

 

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

 

Regards,

Yuliana Gu

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
merelvstr Frequent Visitor
Frequent Visitor

Re: Measure Total Issue - "True'' average

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.
 
 
Highlighted
merelvstr Frequent Visitor
Frequent Visitor

Re: Measure Total Issue - "True'' average

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