Forum Discussion
Average including weighing
Hello,
Imagine I have a dataset with:
| ID | Words | Discount |
| 1 | 10 | 50% |
| 2 | 50 | 40% |
| 3 | 100 | 50% |
| 4 | 20 | 20% |
| Totals 4 | 180 | 40 |
Here, Power BI would tell me that the average Discount is 40% for the 4 rows.
However, I would like to add some kind of "Words" weighting, so for instance ID3 has the biggest weighting.
I have tried several ideas, but not quite finding a way to do it.
I tried to calculate for each row, the % of total words, and just take discount * the %.
But that didn't give me the thing I want as it would be like:
| ID | Words | Discount | % of Total Words | Weighted Discount |
| 1 | 10 | 50% | 5% | 2,5% |
| 2 | 50 | 40% | 28% | 11,2% |
| 3 | 100 | 50% | 56% | 28% |
| 4 | 20 | 20% | 11% | 2,2% |
| Totals 4 | 180 | 40 | 100% | 43,9% |
And this is not at all correct.
On row level, I want the Discount to stay as it is. But on the TOTAL row, I want it to do a weighted average..
Best,
Peter
2 Replies
Hi Anonymous ,
try this:
=DIVIDE(
SUMX('Table'[Discount %]*'Table'[Words]),
CALCULATE(
SUM('Table'[Words]),
REMOVEFILTERS('Table'[ID])
)
)
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi Anonymous ,
sorry, please use this last version.
W_Avg =DIVIDE(SUMX('Table','Table'[Discount %]*'Table'[Words]),CALCULATE(SUM('Table'[Words]),REMOVEFILTERS('Table'[ID])))If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
