cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Frequent Visitor

## DAX Measure For Calculating Stocking Levels

Hi all.

I am trying to create a DAX formula/measure that calculates necessary stocking levels based on several different inputs.  The biggest input is the install base.  Even though it is a very ugly formula in Excel it has worked for a very long time.

To start I've created two parameters to use in the mesaure, install base and lead time.  Both of these can be with a high range for install base and two choices of for the lead time.  I'm stuck at this point.  I've tried SWITCH TRUE (), and an IF statement.  On both I get stuck on the second SUM category so I don't know if it even works beyond that.

This picture shows the dependents within the Excel formula.  I've attached the link to the Excel file:  https://1drv.ms/x/s!Apq9nVxrTY6rlmm42MbpB5046pP3?e=90IZdy.  Entering any number into the 'segment', cell F4, changes the values below in Tier 1 and 2, columns F and G.  If the link doesn't work please let me know.

The formula that I've tried in DAX is as follows:

IF(
SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) > '5',
CALCULATE(
SUM(
MAX(
ROUNDUP(
SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) / (12)),0,
SUM('RIL-Stack 750E - CollectSeg'[TTQ]) + SUM('RIL-Stack 750E - CollectSeg'[TTQ])) - (1))
+
ROUNDUP(
SUM(
SUM(
SUM([Proposed Install Base Value]) * SUM('RIL-Stack 750E - CollectSeg'[Annual Usage]) / 365)
*
)
)
),0
)

I appreciate any guidance on this.  Like I said, I'm at a loss.

Thanks.

Will

1 ACCEPTED SOLUTION
Solution Sage

Hey,

I created your table in Excel in Power BI. Above it slicers for the parameters. You can use the following code to calculate 'Tier 1':

Tier 1 =
VAR _ProposedCS =
SELECTEDVALUE ( ProposedCS[ProposedCS] )
VAR _PropesedLT =
SELECTEDVALUE ( ProposedLT[ProposedLT] )
VAR _AnnualUsage =
SELECTEDVALUE ( 'Table'[Annual Usage] )
VAR _TT =
SELECTEDVALUE ( 'Table'[TTQ] )
RETURN
IF (
_ProposedCS * _AnnualUsage > 5,
VAR _m1 =
MAXX (
{
_TT + _TT - 1,
ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 12 ), 0 )
},
[Value]
)
VAR _m2 =
ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 365 ) * _PropesedLT, 0 )
RETURN
_m1 + _m2,
0
)

You can use the above code as a basis for calculating the other calculations. Adjust the parameters and conditions for this. Hope this helps.

3 REPLIES 3
Solution Sage

Hey,

I created your table in Excel in Power BI. Above it slicers for the parameters. You can use the following code to calculate 'Tier 1':

Tier 1 =
VAR _ProposedCS =
SELECTEDVALUE ( ProposedCS[ProposedCS] )
VAR _PropesedLT =
SELECTEDVALUE ( ProposedLT[ProposedLT] )
VAR _AnnualUsage =
SELECTEDVALUE ( 'Table'[Annual Usage] )
VAR _TT =
SELECTEDVALUE ( 'Table'[TTQ] )
RETURN
IF (
_ProposedCS * _AnnualUsage > 5,
VAR _m1 =
MAXX (
{
_TT + _TT - 1,
ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 12 ), 0 )
},
[Value]
)
VAR _m2 =
ROUNDUP ( DIVIDE ( _ProposedCS * _AnnualUsage, 365 ) * _PropesedLT, 0 )
RETURN
_m1 + _m2,
0
)

You can use the above code as a basis for calculating the other calculations. Adjust the parameters and conditions for this. Hope this helps.

Frequent Visitor

Thank you very much, I really appreciate this!  Hope you have a good weekend.

Solution Sage

Hi @wiser

This looks so messy that I seriously doubt anyone would like to try and decipher this... Might be wrong, of course, but one has to have plenty of time on their hands to deal with such an issue 🙂

Can you please somehow simplify it to only single out the most important bits and make the description more granular/comprehensible? Maybe then people will show more enthusiasm about helping. Just a gentle hint 🙂

Cheers.

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.