Weighted contribution calculation...

[Matty]

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):

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Key Figure[/TH]
[TH]18/12/2017[/TH]
[TH]25/12/2017[/TH]
[TH]01/01/2018[/TH]
[TH]08/01/2018[/TH]
[TH]15/01/2018[/TH]
[TH]22/01/2018[/TH]
[TH]29/01/2018[/TH]
[TH]05/02/2018[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Demand[/TD]
[TD]5[/TD]
[TD]56[/TD]
[TD]45[/TD]
[TD]6[/TD]
[TD]25[/TD]
[TD]50[/TD]
[TD]15[/TD]
[TD]33[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]65[/TD]
[TD]0[/TD]
[TD]15[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Opening Stock[/TD]
[TD]125[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]78[/TD]
[TD]53[/TD]
[TD]18[/TD]
[TD]3[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Closing Stock[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]78[/TD]
[TD]53[/TD]
[TD]18[/TD]
[TD]3[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Days of Supply[/TD]
[TD]24.6[/TD]
[TD]17.6[/TD]
[TD]10.6[/TD]
[TD]15.4[/TD]
[TD]8.4[/TD]
[TD]7.6[/TD]
[TD]0.6[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Demand[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]8[/TD]
[TD]12[/TD]
[TD]50[/TD]
[TD]60[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]55[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]25[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Opening Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]55[/TD]
[TD]47[/TD]
[TD]85[/TD]
[TD]35[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Closing Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]55[/TD]
[TD]47[/TD]
[TD]85[/TD]
[TD]35[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Days of Supply[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]18.9[/TD]
[TD]11.9[/TD]
[TD]11.1[/TD]
[TD]4.1[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Demand[/TD]
[TD]10[/TD]
[TD]15[/TD]
[TD]12[/TD]
[TD]5[/TD]
[TD]8[/TD]
[TD]20[/TD]
[TD]15[/TD]
[TD]55[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]50[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]30[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Opening Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]37[/TD]
[TD]17[/TD]
[TD]2[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Closing Stock[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]45[/TD]
[TD]37[/TD]
[TD]17[/TD]
[TD]2[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Days of Supply[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]0.0[/TD]
[TD]21.3[/TD]
[TD]14.3[/TD]
[TD]7.3[/TD]
[TD]0.3[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Demand[/TD]
[TD]15[/TD]
[TD]71[/TD]
[TD]57[/TD]
[TD]11[/TD]
[TD]41[/TD]
[TD]82[/TD]
[TD]80[/TD]
[TD]148[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Supply[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]0[/TD]
[TD]170[/TD]
[TD]0[/TD]
[TD]65[/TD]
[TD]0[/TD]
[TD]85[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Opening Stock[/TD]
[TD]125[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]178[/TD]
[TD]137[/TD]
[TD]120[/TD]
[TD]40[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Closing Stock[/TD]
[TD]120[/TD]
[TD]64[/TD]
[TD]19[/TD]
[TD]178[/TD]
[TD]137[/TD]
[TD]120[/TD]
[TD]40[/TD]
[TD]0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Days of Supply[/TD]
[TD]13.0[/TD]
[TD]11.5[/TD]
[TD]8.4[/TD]
[TD]18.8[/TD]
[TD]11.8[/TD]
[TD]8.9[/TD]
[TD]1.9[/TD]
[TD]0.0[/TD]
[/TR]
[TR]
[TD]Total[/TD]
[TD]Weighted Days of Supply[/TD]
[TD]24.6[/TD]
[TD]17.6[/TD]
[TD]10.6[/TD]
[TD]18.0[/TD]
[TD]11.2[/TD]
[TD]10.0[/TD]
[TD]3.6[/TD]
[TD]0.0[/TD]
[/TR]
</tbody>[/TABLE]

Weighted Days of Supply is calculated as follows:

=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:

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):

[TABLE="class: tableizer-table"]
<tbody>[TR="class: tableizer-firstrow"]
[TH]Product[/TH]
[TH]Key Figure[/TH]
[TH]18/12/2017[/TH]
[TH]25/12/2017[/TH]
[TH]01/01/2018[/TH]
[TH]08/01/2018[/TH]
[TH]15/01/2018[/TH]
[TH]22/01/2018[/TH]
[TH]29/01/2018[/TH]
[TH]05/02/2018[/TH]
[/TR]
[TR]
[TD]Apple[/TD]
[TD]Weight %[/TD]
[TD]100.0%[/TD]
[TD]100.0%[/TD]
[TD]100.0%[/TD]
[TD]37.6%[/TD]
[TD]29.1%[/TD]
[TD]11.4%[/TD]
[TD]1.3%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]Banana[/TD]
[TD]Weight %[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]32.5%[/TD]
[TD]36.5%[/TD]
[TD]78.3%[/TD]
[TD]98.3%[/TD]
[TD]0.0%[/TD]
[/TR]
[TR]
[TD]Orange[/TD]
[TD]Weight %[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]0.0%[/TD]
[TD]29.9%[/TD]
[TD]34.4%[/TD]
[TD]10.3%[/TD]
[TD]0.4%[/TD]
[TD]0.0%[/TD]
[/TR]
</tbody>[/TABLE]

The formula used here is:

=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

 

[Ozeroth]

Hi Matty,

This is one possible measure that should give you the right result (ignoring any checks you might need for zeros etc) - but please test it
I've defined it from bottom-up based on [Closing Stock] and [Days of Supply], rather than using the [Weighted Days of Supply] measure.

You could define a sub-measure for the red SUMX part, or write in another way that uses [Weighted Days of Supply].

Weight % =DIVIDE (
    [COLOR=#ff0000][B]SUMX ( VALUES ( DimProduct[Product] ), [Closing Stock] * [Days of Supply] )[/B][/COLOR],
    CALCULATE (
        [COLOR=#ff0000][B]SUMX ( VALUES ( DimProduct[Product] ), [Closing Stock] * [Days of Supply] )[/B][/COLOR],
        ALL ( DimProduct )
    )
)

Cheers,
Owen

 

[Matty]

Hi Owen,

Apologies for the late reply...

Thanks for this. I haven't had time to work on the project in the last few days, but I will try the formula out and I'll come back if I have any questions.

Thanks again for your help!

Cheers,

Matty