Forum Discussion
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 shou...
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()))
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
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.
