Calculating the weighted average gives me incorrect values?

[ebecerra]

Hi, I've been looking all morning on how to calculate the weighted average in Power BI and I must be doing something stupid because I just can't seem to get it working as easy as it is in Excel. Let me explain to you my problem which I think is very basic.

This is the data set I have as minimalist as I could write it:

Date	TotalSamples	TimeTaken
11/27/2018	25,000	11,000
11/23/2018	20,000	12,000
11/25/2018	15,000	11,500
11/24/2018	3,000	40,000

I'm trying to calculate the weighted average of time taken for each date to try to better understand the data trend and not have data like the one from 11/24 skew the average time taken. Doing this in Excel is relatively easy, I added a new column to calculate the Weighted average just to see it clearer.

WeightedAverage =(C2*B2)/SUM($B$2:$B$5)

Date	TotalSamples	TimeTaken	WeightedAverage
11/27/2018	25,000	11,000	4,365.08
11/23/2018	20,000	12,000	3,809.52
11/25/2018	15,000	11,500	2,738.10
11/24/2018	3,000	40,000	1,904.76

I'm trying to basically do the same thing in PBI using this measurement:

WAvg = SUMX(Table1,Table1[TimeTaken]*Table1[TotalSamples])/SUM(Table1[TotalSamples])

Then I add a line chart using Date (only the day) in the Axis, and WAvg as the values but it shows me exactly the same data as if I had selected the column TimeTaken. 

 

I was expecting to see something like in Excel:

I must be getting a concept wrong here or doing something stupid. Any help would be appreciated.

Thanks a lot!

[Anonymous · Accepted solution]

Try this: 

 :=
SUMX ( Table1; Table1[TimeTaken] * Table1[TotalSamples] )
    / CALCULATE ( SUM ( [TotalSamples] ); ALL ( Table1 ) )

[Anonymous · Accepted solution]

Try this: 

 :=
SUMX ( Table1; Table1[TimeTaken] * Table1[TotalSamples] )
    / CALCULATE ( SUM ( [TotalSamples] ); ALL ( Table1 ) )

[Anonymous]

@Anonymous you are close, but SumX is not the best in this scenario.  Also its good practice to use the DIVIDE function rather than the divide operator.  Good job though.

[Anonymous]

@Anonymous 
If there is more than one entry for the same day your calculation will fail.

If you have two entries with the same sample size and time, they should be equivalent to one entry with double the sample size and same time. My proposed solution works.

In the picture below Wa2 is my solution and wg3 is yours.

I get the same result for the 25 and 27. 

[Anonymous]

Hi @Anonymous you must have done something wrong, because our answers will be the same:

[Anonymous]

@Anonymous I said if you have multiple entries for the same day.

[Anonymous · Accepted solution]

You're right.  I stand corrected.  I didn't include the 2nd entry in my sample data for those screenshots.  So i agree the solution should be:

wa2 = DIVIDE(
    SUMX ( Table1, Table1[TimeTaken] * Table1[TotalSamples] ),
    CALCULATE ( 
        SUM ( [TotalSamples] ), 
        ALL ( Table1 ) 
    )
)

[ebecerra]

Thanks everyone! That did the trick. I had to modify the ALL part inside the CALCULATE to use ALLSELECTED since I had more columns in my table and it was jut not picking them up with the slicers I had set.

Thank you very much!

[Anonymous]

Create your measure like this:

WAvg = var numerator = SUM(Table1[TimeTaken]) * SUM(Table1[TotalSamples])
var denominator = CALCULATE(
	SUM(Table1[TotalSamples]),
	ALL(Table1)
)
RETURN

DIVIDE(numerator, denominator)

And you get this: