Forum Discussion
Tiered Pricing Formula
TylerPeplinski See if this is what you are looking for:
Column = VAR __Item = [Item] VAR __ProposedPrice = [Proposed Price] VAR __Qty = [Min Qty] VAR __MinQty = MINX(FILTER('Table12',[Item]=__Item && [Min Qty]<__Qty),[Min Qty]) VAR __ProposedMinQtyPrice = MAXX(FILTER('Table12',[Item]=__Item && [Min Qty]=__MinQty),[Proposed Price]) RETURN IF(ISBLANK(__MinQty),FALSE(),IF(__ProposedPrice > __ProposedMinQtyPrice ,TRUE(),FALSE()))
Greg_Deckler - Greg thanks for the reply. I tried showing in my screenshots above. I have provided more examples in excel. I need a formula in power BI that will tell me if a part's proposed price is higher for the lowest min qty. In my screenshot you can see the formula i used in excel. I just want a simple true or false, so i can see where the tiered pricing doesn't make sense. For example in my screenshot, if part 0129431 had a proposed price of $120 for the min qty line of 1, it would say TRUE and that would trigger us to say this isn't correct because if you buy less qty of a part it shouldn't be cheaper than if you bought more... I hope this helps clear it up a little more.
Community ChampionTylerPeplinski Yeah, I just don't like to type if possible which is why data posted as text is better as it can be copied and pasted.
Greg_Deckler - Does this work? I was going to attach it but didn't see that option to begin with
Item Current Price Proposed Price Min Qty Formula 0129431 137.03760 152.11200 1 FALSE 0129431 123.33380 136.90080 5 FALSE 0200725 32.75370 35.04600 4 FALSE 0200725 31.77110 33.99460 80 FALSE 0200772 11.89440 12.72600 5 FALSE 0200772 8.32610 8.90820 25 FALSE 0200772 7.73140 8.27190 50 FALSE 0200773 14.52150 15.54000 5 FALSE 0200773 10.16510 10.87800 25 FALSE 0200773 9.43900 10.10100 50 FALSE 0209325 10.81710 12.00600 12 FALSE 0209325 10.40110 11.54420 120 FALSE 0209325 10.00110 11.10030 240 FALSE 0211480 7.13790 7.92600 12 FALSE 0211480 6.86340 7.62120 120 FALSE 0211480 6.59940 7.32810 360 FALSE 0211481 9.32400 10.35000 10 FALSE 0211481 8.96540 9.95190 100 FALSE 0211481 8.62060 9.56920 300 FALSE - Greg_Deckler
TylerPeplinski See if this is what you are looking for:
Column = VAR __Item = [Item] VAR __ProposedPrice = [Proposed Price] VAR __Qty = [Min Qty] VAR __MinQty = MINX(FILTER('Table12',[Item]=__Item && [Min Qty]<__Qty),[Min Qty]) VAR __ProposedMinQtyPrice = MAXX(FILTER('Table12',[Item]=__Item && [Min Qty]=__MinQty),[Proposed Price]) RETURN IF(ISBLANK(__MinQty),FALSE(),IF(__ProposedPrice > __ProposedMinQtyPrice ,TRUE(),FALSE()))Greg_Deckler Greg - Do you think you could help me with this issue we are having? For the example in the screenshot, we want it to say TRUE. I think the problem is that the MOQ is the same. Please let me know your thoughts.