Hi guys,
i need to create a weighted average measure for the following scenario:
I have 3 SKU on stock with a stock reach of 10 days each (Col: Stock reach per Qty).
They all have 5 quantities on stock (Col: Average Qty on stock).
But they have different volume (Col: Volume per stock unit).
Now i want to weight the total stock reach of the quantities with the volume. To illustrate i created the two Helping Columns where i calculate what i need for the weighted average. The result is the Col: Total Days Stock reach weighted. This column i want to create with a measure. But without the helping columns since i dont want to create them in the Power BI Model. Here the example:
Helping Columns | ||||||||
SKU | Stock reach per Qty | Average Qty on Stock | Volume per Stock Unit | Total Days Stock reach | Total Volume for stock reach | Total Days Stock reach weighted | Calculation | |
123 | 10 | 5 | 1 | 50 | 50 | 0,45 | =50*50/5550 | |
1234 | 10 | 5 | 10 | 50 | 500 | 4,50 | =50*500/5550 | |
12345 | 10 | 5 | 100 | 50 | 5000 | 45,05 | =50*5000/5550 | |
TOTAL | 5550 |
How must i create the measure for this column?
Thanks guys!
Solved! Go to Solution.
@Anonymous ,Try like
divide(sumx(all(Table),Table[Stock Unit]*Table[Stock reach]*Table[Stock reach]) ,sumx(all(Table),Table[Stock Unit]*Table[Stock reach]))
@Anonymous ,
So 'Total Days Stock reach' and 'Total volumn for stock reach' are two calculate columns, right? Could you also share the dax expression of the two calculate columns?
Community Support Team _ Jimmy Tao
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
@Anonymous ,Try like
divide(sumx(all(Table),Table[Stock Unit]*Table[Stock reach]*Table[Stock reach]) ,sumx(all(Table),Table[Stock Unit]*Table[Stock reach]))
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!
User | Count |
---|---|
107 | |
74 | |
66 | |
49 | |
48 |
User | Count |
---|---|
164 | |
87 | |
77 | |
70 | |
67 |