Calculate Measure With Two Parameters for Every Row in Table

[littlemojopuppy]

Hi...I might be overthinking this but I've gone around in circles for hours with this.  I'd appreciate any help anyone is willing to give.  I'm trying to calculate the economic order quantity for a bunch of items in inventory.  The general formula - NOT INTENDING THIS TO BE DAX SO DON'T TREAT IT AS SUCH - would be this: SQRT(2 * AnnualDemand * (ItemOrderingCost + CommonOrderCost) / (Holding Cost * Unit Price).  It should calculate based on the historical demand for that product, that product's unit price and two parameters: Common Order Cost and Holding Cost %.  This could easily be accomplished except I have two parameters, so using a calculated column is not an option.

The measure is intended to calculate based on the values in each individual row of the table.  Here's the code I'm trying:

EOQ = 
    VAR
        ItemNumber = SELECTEDVALUE(IA_TSTOCK_WEEKLY[ANX_ITEM_I])
    RETURN

    SQRT(
        SUMX(
            FILTER(
                IA_TSTOCK_WEEKLY,
                IA_TSTOCK_WEEKLY[ANX_ITEM_I] = ItemNumber
            ),
            2 * IA_TSTOCK_WEEKLY[12MonthUsage] * (IA_TSTOCK_WEEKLY[Unit Price] + 'Common Order Cost'[Common Order Cost Value]) / ('Holding Cost'[Holding Cost Value] * IA_TSTOCK_WEEKLY[Unit Price])
        )
    )

And here's the results:

The formula is incorrect because it's calculating $3 for every single product, which is dead wrong.  And I know it's because I'm using the iterator SUMX in the formula.  I've tried doing this without the iterator and I get the error that it can't calculate because there is more than one value in the column....I have tried Googling and read every article I could find, writing and rewriting this for hours a number of different ways and I'm at my wits end with it.  Could someone please point me to what's wrong and how to fix it?

Thank you!

[v-yulgu-msft]

Hi @littlemojopuppy,

Please try this measure:

EOQ = 
    VAR
        ItemNumber = SELECTEDVALUE(IA_TSTOCK_WEEKLY[ANX_ITEM_I])
    RETURN

    SQRT(
        SUMX(
            FILTER(
                ALLSELECTED(IA_TSTOCK_WEEKLY),
                IA_TSTOCK_WEEKLY[ANX_ITEM_I] = ItemNumber
            ),
            2 * IA_TSTOCK_WEEKLY[12MonthUsage] * (IA_TSTOCK_WEEKLY[Unit Price] + 'Common Order Cost'[Common Order Cost Value]) / ('Holding Cost'[Holding Cost Value] * IA_TSTOCK_WEEKLY[Unit Price])
        )
    )

Best regards,

Yuliana Gu

[littlemojopuppy]

Hi Yuliana -

it worked!  Winner winner chicken dinner!

Thank you again for your help!!!  

-David

[littlemojopuppy · Accepted solution]

So the solution I was trying was very resource intensive.  After thinking about it, there was no need for the filter or ALLSELECTED...the row context took care of the filtering.  While the other code works, it's not efficient.  This worked MUCH better...

EOQ = 
    SQRT(
        SUMX(
            'Stock Weekly',
            (2 * 'Stock Weekly'[12MonthUsage] * 'Ordering Cost'[Ordering Cost Value]) / (('Holding Cost'[Holding Cost Value]/100) * 'Stock Weekly'[Unit Price])
        )
    )

[littlemojopuppy]

Good morning!  And thank you for your help!

I tried your suggestion and...after some time, got an error that PBI cannot display the visual (out of memory).  So I'm going to take it home and try it on a real computer and see what happens...

But thank you for your help!!!