Forum Discussion

littlemojopuppy's avatar
littlemojopuppy
Community Champion
7 years ago
Solved

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 b...
Need Help
Tutorial Requests
  • littlemojopuppy's avatar
    littlemojopuppy
    7 years ago

    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])
        )
    )
v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
7 years ago

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's avatar
    littlemojopuppy
    Community Champion
    7 years ago

    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's avatar
    littlemojopuppy
    Community Champion
    7 years ago

    Hi Yuliana -

    it worked!  Winner winner chicken dinner!

    Thank you again for your help!!!  

     

    -David

    • littlemojopuppy's avatar
      littlemojopuppy
      Community Champion
      7 years ago

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