Weighted contribution calculation...

Matty

Well-known Member
Joined
Feb 17, 2007
Messages
3,717
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:

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

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

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
 

Excel Facts

Formula for Yesterday
Name Manager, New Name. Yesterday =TODAY()-1. OK. Then, use =YESTERDAY in any cell. Tomorrow could be =TODAY()+1.
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].

Code:
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
 
Upvote 0
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
 
Upvote 0

Forum statistics

Threads
1,224,823
Messages
6,181,178
Members
453,021
Latest member
Justyna P

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top