Forum Discussion
Weighted Average by Region Calculation not working
Hello
I am trying to get the Weighted Average by Region next to my average DailyRev., but the numbers are really high I could not figure out why is that. Can anybody please check this and let me know what's going on? I spent a lot of hours and I am stumped. I have filtered for the last 12 months, but it should not affect it.
Formula
Rev BY Region =
VAR TotalDailyRev =
CALCULATE(
SUM(Sampledata[DailyRev]),
ALL(Sampledata[Region_Adj])
)
VAR SummerisedTable =
ADDCOLUMNS(
SUMMARIZE(
Sampledata,
Sampledata[Region_Adj]
),
"DailyRev", SUM(Sampledata[DailyRev]),
"WT", SUM(Sampledata[DailyRev])/TotalDailyRev
)
RETURN
SUMX(
SummerisedTable,
[WT] * [DailyRev]
)6 Replies
- Ahmedx
Super User
Is this what you are looking for?
- Zack92
Helper III
Thank you for the quick response.
Not quite right, I looking for the weighted average for each region. Not the %. I need the 12-month filter. I don't think we need to remove the filter, right
Again, thank you - Ahmedx
Super User
you can show the expected output?
- Zack92
Helper III
To be honest, I am not sure. That is why seeking help in Community. I am trying to check the way I calculated my weighted average is correct or wrong. When Comparing below, my average and my calculated weighted column, it has a very high number. which I don't think is correct (not 100% sure) .
I just want to check with you guys if my method is correct.Thank you
- Ahmedx
Super User
I can't understand your business logic without the expected numbers.
But maybe you are waiting for this?! - Zack92
Helper III
Thanks for the post, but I found the correct way to calculate the weighted average in my situation. Weighted_Avg = ABS(CALCULATE(SUM(AppendCombined[Revenue])/(SUM(AppendCombined[Hours])/8)))
This is the correct formula for my situation to get it. Again, thank you for the help