Hi Team,
Looking for some DAX help again!
Here's a mock up built in Excel to illustrate what I'm trying to achieve (data resides in range B2:K23):
<tbody>
</tbody>
Weighted Days of Supply is calculated as follows:
The formula (array entered) multiplies Closing Stock by Days of Supply for each Product, and then divides the result by the Total Closing Stock.
I have managed to replicate this in DAX as follows:
I now want to create the following (this Excel mock up resides in range B25:K28):
<tbody>
</tbody>
The formula used here is:
It is calculating the weight (Closing Stock * Days of Supply) of the Product referenced, divides this by the Total Closing Stock and then divides this result by the Weighted Days of Supply calculated previously. This then gives a weight (in percentage) for each Product.
I'm trying to replicate this logic using DAX, but I'm hitting a wall on the last bit. Can anyone help?
Cheers,
Matty
Looking for some DAX help again!
Here's a mock up built in Excel to illustrate what I'm trying to achieve (data resides in range B2:K23):
Product | Key Figure | 18/12/2017 | 25/12/2017 | 01/01/2018 | 08/01/2018 | 15/01/2018 | 22/01/2018 | 29/01/2018 | 05/02/2018 |
---|---|---|---|---|---|---|---|---|---|
Apple | Demand | 5 | 56 | 45 | 6 | 25 | 50 | 15 | 33 |
Apple | Supply | 0 | 0 | 0 | 65 | 0 | 15 | 0 | 30 |
Apple | Opening Stock | 125 | 120 | 64 | 19 | 78 | 53 | 18 | 3 |
Apple | Closing Stock | 120 | 64 | 19 | 78 | 53 | 18 | 3 | 0 |
Apple | Days of Supply | 24.6 | 17.6 | 10.6 | 15.4 | 8.4 | 7.6 | 0.6 | 0.0 |
Banana | Demand | 0 | 0 | 0 | 0 | 8 | 12 | 50 | 60 |
Banana | Supply | 0 | 0 | 0 | 55 | 0 | 50 | 0 | 25 |
Banana | Opening Stock | 0 | 0 | 0 | 0 | 55 | 47 | 85 | 35 |
Banana | Closing Stock | 0 | 0 | 0 | 55 | 47 | 85 | 35 | 0 |
Banana | Days of Supply | 0.0 | 0.0 | 0.0 | 18.9 | 11.9 | 11.1 | 4.1 | 0.0 |
Orange | Demand | 10 | 15 | 12 | 5 | 8 | 20 | 15 | 55 |
Orange | Supply | 0 | 0 | 0 | 50 | 0 | 0 | 0 | 30 |
Orange | Opening Stock | 0 | 0 | 0 | 0 | 45 | 37 | 17 | 2 |
Orange | Closing Stock | 0 | 0 | 0 | 45 | 37 | 17 | 2 | 0 |
Orange | Days of Supply | 0.0 | 0.0 | 0.0 | 21.3 | 14.3 | 7.3 | 0.3 | 0.0 |
Total | Demand | 15 | 71 | 57 | 11 | 41 | 82 | 80 | 148 |
Total | Supply | 0 | 0 | 0 | 170 | 0 | 65 | 0 | 85 |
Total | Opening Stock | 125 | 120 | 64 | 19 | 178 | 137 | 120 | 40 |
Total | Closing Stock | 120 | 64 | 19 | 178 | 137 | 120 | 40 | 0 |
Total | Days of Supply | 13.0 | 11.5 | 8.4 | 18.8 | 11.8 | 8.9 | 1.9 | 0.0 |
Total | Weighted Days of Supply | 24.6 | 17.6 | 10.6 | 18.0 | 11.2 | 10.0 | 3.6 | 0.0 |
<tbody>
</tbody>
Weighted Days of Supply is calculated as follows:
Code:
=IFERROR(SUM(IF($C6:$C16="Closing Stock",D6:D16*D7:D17))/D21,0)
The formula (array entered) multiplies Closing Stock by Days of Supply for each Product, and then divides the result by the Total Closing Stock.
I have managed to replicate this in DAX as follows:
Code:
Weighted Stock Cover =
IF (
[Closing Stock] = 0,
0,
SUMX (
VALUES ( DimProduct[Product] ),
[Closing Stock] * [Days of Supply)]
)
/ [Closing Stock]
)
I now want to create the following (this Excel mock up resides in range B25:K28):
Product | Key Figure | 18/12/2017 | 25/12/2017 | 01/01/2018 | 08/01/2018 | 15/01/2018 | 22/01/2018 | 29/01/2018 | 05/02/2018 |
---|---|---|---|---|---|---|---|---|---|
Apple | Weight % | 100.0% | 100.0% | 100.0% | 37.6% | 29.1% | 11.4% | 1.3% | 0.0% |
Banana | Weight % | 0.0% | 0.0% | 0.0% | 32.5% | 36.5% | 78.3% | 98.3% | 0.0% |
Orange | Weight % | 0.0% | 0.0% | 0.0% | 29.9% | 34.4% | 10.3% | 0.4% | 0.0% |
<tbody>
</tbody>
The formula used here is:
Code:
=IFERROR(SUM(IF($B$6:$B$16=$B26,IF($C$6:$C$16="Closing Stock",D$6:D$16*D$7:D$17))/D$21/D$23),0)
It is calculating the weight (Closing Stock * Days of Supply) of the Product referenced, divides this by the Total Closing Stock and then divides this result by the Weighted Days of Supply calculated previously. This then gives a weight (in percentage) for each Product.
I'm trying to replicate this logic using DAX, but I'm hitting a wall on the last bit. Can anyone help?
Cheers,
Matty