Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
6 years ago
Solved

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 ple...
Need Help
  • v-eachen-msft's avatar
    v-eachen-msft
    6 years ago

    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.

     

amitchandak's avatar
amitchandak
Super User
6 years ago

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      6 years ago
      SupplierProductPriceQtyTotal Price This is what i need
      1Product A$3,317.2780$53,166  
      2Product A$3,425.0380$54,609 (54609/53166-1)
      3Product A$3,941.0080$63,430 (63430/53166-1)
            and so on
             
             
      1Product b$6,461.5994$121,702  
      2Product b$12,536.6994$234,382  
      3Product b$13,715.1594$256,124  
             
             
      1Product C$8,852.5175$132,975  
      2Product C$9,347.3775$139,893  
      3Product C$9,870.8575$146,686  
             
             
             
             
      1Product D$5,103.991000$1,022,955  
      2Product D$5,254.121000$1,047,235  
      3Product D$5,526.341000$1,104,031  
      • v-eachen-msft's avatar
        v-eachen-msft
        Community Support
        6 years ago

        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.