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()))
Community ChampionTylerPeplinski Hard to say, maybe a variation on MTBF? Would be easier if you posted sample data and expected output. Sorry, having trouble following, can you post sample data as text and expected output?
Not really enough information to go on, please first check if your issue is a common issue listed here: https://community.powerbi.com/t5/Community-Blog/Before-You-Post-Read-This/ba-p/1116882
Also, please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490
The most important parts are:
1. Sample data as text, use the table tool in the editing bar
2. Expected output from sample data
3. Explanation in words of how to get from 1. to 2.
See my article on Mean Time Between Failure (MTBF) which uses EARLIER: http://community.powerbi.com/t5/Community-Blog/Mean-Time-Between-Failure-MTBF-and-Power-BI/ba-p/339586.
The basic pattern is:
Column =
VAR __Current = [Value]
VAR __PreviousDate = MAXX(FILTER('Table','Table'[Date] < EARLIER('Table'[Date])),[Date])
VAR __Previous = MAXX(FILTER('Table',[Date]=__PreviousDate),[Value])
RETURN
__Current - __Previous
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.
- Greg_Deckler
TylerPeplinski 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()))