cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

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

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

View solution in original post

4 REPLIES 4
v-yulgu-msft Super Contributor
Super Contributor

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.

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

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

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

View solution in original post

Helpful resources

Announcements
Can You Solve These Challenge

Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

Community News & Announcements

Community News & Announcements

Get your latest community news and announcements.

Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Community Kudopalooza

Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Users Online
Currently online: 377 members 3,682 guests
Please welcome our newest community members: