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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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
Anonymous
Not applicable

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
 
 

View solution in original post

3 REPLIES 3
v-yulgu-msft
Employee
Employee

Hi @Anonymous,

 

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.
Anonymous
Not applicable

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
Not applicable

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
 
 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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