Conditional Weighted Average

[lager1001]

Need help with a weighted average formula. I've tried AverageIfs but get #DIV/0 errors because some of the numbers being calculated in the average are "0".

See below example.

There are 4 unique accounts (but 7 line items) in CW24, each with their respective Score. If I do a simple average, it takes all scores for all line items and I get 63.86. What I need is the weighted average so it calculates each score only once for each account and provide the average by the unique number of accounts for the calendar week. The desired outcome is the "Weighted Average Column" which would be 70.39+65.45+99.96+0.00 / 4. Some of my scores are 0.00 so these have to be inclusive to the formula. Can anyone assist?

Year/CW	Account	Score	Average Score	Weighted Average
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	ABC	70.39	63.86	58.95
2020-CW24	DEF	65.45	63.86	58.95
2020-CW24	GHI	99.96	63.86	58.95
2020-CW24	JKL	0.00	63.86	58.95

 

[lager1001]

You probably need to confirm the formula with Ctrl Shift Enter, rather than just enter & then fill it down.

I see. It won't allow it because the report is a table. "Multi-cell array formulas aren't allowed in tables". I will have to find a different approach. Thanks for trying.

 

[CA_Punit]

The same formula with Excel table is working in our sheet why is it not working in yours???

I suppose you are making some mistake. I insist please try it will help

 

[Fluff]

Delete the formula from the table, then re-enter it in the first row and confirm it with Ctrl Shift Enter.
If the formula does not automatically propagate down the entire table, then drag it down.

 

[CA_Punit]

First 1. Copy and paste the data from fluff post.
2. Delete the formula column ie D
3. Go to B2 and Press Ctrl T
4, Press Ok
5. Copy the formula from Fluff post and paste it on D2 with Ctrl+Shift+Enter

 

[lager1001]

Working great now. This one has been frustrating me for days and days. Thanks all!

 

[Fluff]

Glad we could help & thanks for the feedback.

 

[lager1001]

Glad we could help & thanks for the feedback.

Possible to get this to work with an additional IF statement out front? If Date >= CutOff Date then (this formula) else 0.00.

Tried this approach but doesn't seem to be working with the Ctrl+Shift+Enter nature of the weighted average. Any ideas?

 

[lager1001]

=AVERAGE(IFERROR(C2:C8/IF(FREQUENCY(MATCH(B2:B8,B2:B8,0),MATCH(B2:B8,B2:B8,0))>0,1,0),FALSE))

Moving this report into Power BI. Can you assist with getting this same output in DAX? I'm trying different arguments to see if I can get it but thought I'd drop a post here to see if anyone could help.

Thank you.

 

[Fluff]

As this is a totally different question, you need to start a new thread in the Power BI section. Thanks.