cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
littlemojopuppy Regular Visitor
Regular Visitor

Calculate Measure With Two Parameters for Every Row in Table

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:

Capture.PNG
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!

1 ACCEPTED SOLUTION

Accepted Solutions
littlemojopuppy Regular Visitor
Regular Visitor

Re: Calculate Measure With Two Parameters for Every Row in Table

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])
        )
    )
4 REPLIES 4
Community Support Team
Community Support Team

Re: Calculate Measure With Two Parameters for Every Row in Table

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

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
littlemojopuppy Regular Visitor
Regular Visitor

Re: Calculate Measure With Two Parameters for Every Row in Table

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 (oout 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!!!

littlemojopuppy Regular Visitor
Regular Visitor

Re: Calculate Measure With Two Parameters for Every Row in Table

Hi Yuliana -

it worked!  Winner winner chicken dinner!

Thank you again for your help!!!  

 

-David

littlemojopuppy Regular Visitor
Regular Visitor

Re: Calculate Measure With Two Parameters for Every Row in Table

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