Forum Discussion
Variance between rows, without date
I have a list of price per supplier and i need to determine the best supplier based on price, i need to do a division between min price and next min price , to get the % of variance . Can someone please help?
Supplier Price Variance
1 100
2 101 what i need is ( 101/100-1)
3 120 (120/101-1)
and so one
Hi Anonymous ,
Do you mean to find the variance in each product?
You could refer to the following column:
Column 2 = VAR a = CALCULATE ( MIN ( 'Table'[Total Price] ), ALLEXCEPT ( 'Table', 'Table'[Product] ) ) RETURN 'Table'[Total Price] / a - 1To prevent misunderstanding, I created another two columns for your reference (Column1 & Column3).
Here is my test file.
4 Replies
Anonymous , I do see any mention of items or material. But I used Item in the formula, Create new column
new column =
var _min =minx(filter(Table,[item]=earlier[item]),[Price])
return
if([Price]=_min,blank(),divide([Price],_min))without item
new column =
var _min =minx(Table,[Price])
return
if([Price]=_min,blank(),divide([Price],_min))Supplier Product Price Qty Total Price This is what i need 1 Product A $3,317.27 80 $53,166 2 Product A $3,425.03 80 $54,609 (54609/53166-1) 3 Product A $3,941.00 80 $63,430 (63430/53166-1) and so on 1 Product b $6,461.59 94 $121,702 2 Product b $12,536.69 94 $234,382 3 Product b $13,715.15 94 $256,124 1 Product C $8,852.51 75 $132,975 2 Product C $9,347.37 75 $139,893 3 Product C $9,870.85 75 $146,686 1 Product D $5,103.99 1000 $1,022,955 2 Product D $5,254.12 1000 $1,047,235 3 Product D $5,526.34 1000 $1,104,031
