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

ProductKey Figure18/12/201725/12/201701/01/201808/01/201815/01/201822/01/201829/01/201805/02/2018
AppleDemand55645625501533
AppleSupply00065015030
AppleOpening Stock12512064197853183
AppleClosing Stock120641978531830
AppleDays of Supply24.617.610.615.48.47.60.60.0
BananaDemand00008125060
BananaSupply00055050025
BananaOpening Stock000055478535
BananaClosing Stock000554785350
BananaDays of Supply0.00.00.018.911.911.14.10.0
OrangeDemand10151258201555
OrangeSupply0005000030
OrangeOpening Stock00004537172
OrangeClosing Stock00045371720
OrangeDays of Supply0.00.00.021.314.37.30.30.0
TotalDemand15715711418280148
TotalSupply000170065085
TotalOpening Stock125120641917813712040
TotalClosing Stock1206419178137120400
TotalDays of Supply13.011.58.418.811.88.91.90.0
TotalWeighted Days of Supply24.617.610.618.011.210.03.60.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):

ProductKey Figure18/12/201725/12/201701/01/201808/01/201815/01/201822/01/201829/01/201805/02/2018
AppleWeight %100.0%100.0%100.0%37.6%29.1%11.4%1.3%0.0%
BananaWeight %0.0%0.0%0.0%32.5%36.5%78.3%98.3%0.0%
OrangeWeight %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
 

Excel Facts

Whats the difference between CONCAT and CONCATENATE?
The newer CONCAT function can reference a range of cells. =CONCATENATE(A1,A2,A3,A4,A5) becomes =CONCAT(A1:A5)
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,213,536
Messages
6,114,211
Members
448,554
Latest member
Gleisner2

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