Forum Discussion

TylerPeplinski's avatar
TylerPeplinski
Frequent Visitor
5 years ago
Solved

Tiered Pricing Formula

Hello,

 

I'm trying to figure out a formula that will tell me if the tiered pricing makes sense. For example a part number with the lowest qty should have the highest price and the highest qty should have the lowest price. I know how to do this in excel, but can't figure it out in BI. In excel I sort the data by part number and then qty. Then the formula is =IF(Part Number = Part Number, IF(Proposed Price of lower qty>=Proposed Price of higher qty, TRUE,FALSE),FALSE). I have pasted my excel sheet and power BI. Let me know if this is possible. 

 

Need Help
  • Greg_Deckler's avatar
    Greg_Deckler
    5 years ago

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

     

8 Replies

  • Greg_Deckler's avatar
    Greg_Deckler
    Icon for Community Champion rankCommunity Champion
    5 years ago

    TylerPeplinski 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

    • TylerPeplinski's avatar
      TylerPeplinski
      Frequent Visitor
      5 years ago

      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's avatar
        Greg_Deckler
        Icon for Community Champion rankCommunity Champion
        5 years ago

        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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Hi  TylerPeplinski ,

    Here are the steps you can follow：

    1. Through power query – Add Column – Index Column - From 1.

    2. Create calculated column.

    Column =
var _rank1=CALCULATE(SUM('Table'[Current Price]),FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Index]=EARLIER('Table'[Index])))
var _rank2=CALCULATE(SUM('Table'[Current Price]),FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Index]=EARLIER('Table'[Index])+1))
var _Item1=CALCULATE(SUM('Table'[Item]),FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Index]=EARLIER('Table'[Index])))
var _Item2=CALCULATE(SUM('Table'[Item]),FILTER(ALL('Table'),'Table'[Item]=EARLIER('Table'[Item])&&'Table'[Index]=EARLIER('Table'[Index])+1))
return
IF(
    _Item1=_Item2,
    IF(
        _rank2>_rank1,TRUE(),FALSE()),FALSE())

    3. Result:

     

    Best Regards,

    Liu Yang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

    • TylerPeplinski's avatar
      TylerPeplinski
      Frequent Visitor
      4 years ago

      Anonymous - I don't think this will work because the item has to be in text, so that the leading zeros in the part number don't drop off.