Forum Discussion

marsclone's avatar
marsclone
Helper IV
7 years ago
Solved

Measure for average price

Hello,

 

I have a table with amount and quantity per location, per product, etc.

What is the best measure to calculate the average price.

At this moment i have the following:

 

Average Price = if(ISERROR(sum(Data[Revenu]) / sum(Data[Quantity]));"";(sum(Data[Revenu]) / sum(Data[Quantity])))
 
I also would like to know, how i can calculate the difference between the price from 2018 and 2017?
 
My testfile is:
 
 
Thank you!
Regards Marcel

 

Need Help
  • v-piga-msft's avatar
    v-piga-msft
    7 years ago

    Hi marsclone ,

    Do you want to get the output below?

    If it is, you could create the measure below.

    difference price =
VAR average_price_2017 =
    CALCULATE ( [Average Price], FILTER ( 'Date', 'Date'[Year] = 2017 ) )
VAR average_price_2018 =
    CALCULATE ( [Average Price], FILTER ( 'Date', 'Date'[Year] = 2018 ) )
RETURN
    average_price_2018 - average_price_2017

    If you still need help, please share your desired output.

    In addition, please mask sensitive data before uploading.

    Best Regards,

    Cherry

     

2 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Resident Rockstar
    7 years ago

    Hi marsclone ,

    Do you want to get the output below?

    If it is, you could create the measure below.

    difference price =
VAR average_price_2017 =
    CALCULATE ( [Average Price], FILTER ( 'Date', 'Date'[Year] = 2017 ) )
VAR average_price_2018 =
    CALCULATE ( [Average Price], FILTER ( 'Date', 'Date'[Year] = 2018 ) )
RETURN
    average_price_2018 - average_price_2017

    If you still need help, please share your desired output.

    In addition, please mask sensitive data before uploading.

    Best Regards,

    Cherry

     

    • marsclone's avatar
      marsclone
      Helper IV
      7 years ago

      v-piga-msft 

       

      Hello Cherry,

       

      Thank you for your help! This will work!

       

      Is this a better solution instead of measures calculating (Amount YTD & Quantity YTD) and (Amount LY & Quantity LY) and divide those and substract?

       

      Regards Marcel