Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Solved! Go to Solution.
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 - 1
To prevent misunderstanding, I created another two columns for your reference (Column1 & Column3).
Here is my test file.
@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))
Yes, I have a supplier and a product , the price is for the product. I need to get the % variance between the chepeast one to the next and so on.
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 |
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 - 1
To prevent misunderstanding, I created another two columns for your reference (Column1 & Column3).
Here is my test file.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |