Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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
 
Need Help
  • Anonymous's avatar
    Anonymous
    7 years ago

    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

  • v-yulgu-msft's avatar
    v-yulgu-msft
    Microsoft Employee
    7 years ago

    Hi Anonymous,

     

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

     

    Regards,

    Yuliana Gu

    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      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's avatar
        Anonymous
        Not applicable
        7 years ago

        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