- Forums
- Get Help with Power BI
- Desktop
- Service
- Report Server
- Power Query
- Mobile Apps
- Developer
- DAX Commands and Tips
- Let's Talk Data
- Custom Visuals Development Discussion
- Community Support
- Community Information
- Community Accounts & Registration
- Using the Community
- Community Feedback
- Training and Consulting
- Dashboard in a Day
- EdX Specific Training Discussion Forum

Turn on suggestions

Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.

Showing results for

- Microsoft Power BI Community
- Forums
- Get Help with Power BI
- Desktop
- Re: Calculate Measure With Two Parameters for Ever...

Topic Options

- Subscribe to RSS Feed
- Mark Topic as New
- Mark Topic as Read
- Float this Topic for Current User
- Bookmark
- Subscribe
- Printer Friendly Page

littlemojopuppy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-08-2018
12:53 PM

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!

Solved! Go to Solution.

1 ACCEPTED SOLUTION

Accepted Solutions

Highlighted

littlemojopuppy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-22-2018
10:51 AM

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

v-yulgu-msft

Community Support Team

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018
12:58 AM

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.

If this post

littlemojopuppy

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018
08:11 AM

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

littlemojopuppy

Member

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

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-09-2018
02:19 PM

Hi Yuliana -

it worked! Winner winner chicken dinner!

Thank you again for your help!!!

-David

Highlighted

littlemojopuppy

Member

- Mark as New
- Bookmark
- Subscribe
- Subscribe to RSS Feed
- Permalink
- Email to a Friend
- Report Inappropriate Content

11-22-2018
10:51 AM

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