Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Calculate Price variance YoY

I have a table structured with prices, and I would like to understand the price difference. I am plotting everything in a matrix. I have a "Price ID" that is just a combination of different columns that represent a sales of a specific materials to a specific customer, when this is occurring in the different years (I have another column for that) I would need to: filter Price ID for the same value, check if is repeated for 2022 and 2023 and in case is repeated to take the column related to the price and simply calculate the YoY difference 2023 - 2022.

 

I am currently using this formula here:

Price Difference =

VAR Current_Year = 'Grouped 2023 Rev'[Year]
VAR Previous_Year =Current_Year-1

RETURN
    IF(COUNTROWS(FILTER('Grouped 2023 Rev','Grouped 2023 Rev'[Year]=Current_Year && 'Grouped 2023 Rev'[Price ID] = SELECTEDVALUE('Grouped 2023 Rev'[Price ID])))=1,
    BLANK(),
    CALCULATE(
        SUMX(
            FILTER('Grouped 2023 Rev','Grouped 2023 Rev'[Year]= Current_Year),
            'Grouped 2023 Rev'[Unitary Price FC]
        )-
        sumx(
            filter('Grouped 2023 Rev','Grouped 2023 Rev'[Year]= Previous_Year),
            'Grouped 2023 Rev'[Unitary Price FC]
        )
    )
    )
 
But does not calculate the delta, just reporting the same unitary price in the designated new column.
Any idea of how to solve this?
Thank you
Need Help
  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    3 years ago

    Hi , Anonymous 

    According to your description, you wan to calculate the differnce between the 2022 and 2023 if have the same PriceID , this is my test data and my understand for your description :

     

    Here are the steps you can refer to .

    We can click "New Measure" to create a measure like this:

    Measure = var _year = MAX('Table'[Year])
var _priceid= MAX('Table'[Price ID])
var _same = FILTER(ALLSELECTED('Table') , 'Table'[Price ID] = _priceid && 'Table'[Year] =_year-1)
var _cur_year = SUM('Table'[Unitary Price FC])
var _last_year = SUMX(_same , [Unitary Price FC])
return
IF(COUNTROWS(_same)>0,_cur_year-_last_year)

     

    Then we can put the fields we need on the visual and we can meet your need:

     

     

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

     

2 Replies

  • v-yueyunzh-msft's avatar
    v-yueyunzh-msft
    Icon for Community Support rankCommunity Support
    3 years ago

    Hi , Anonymous 

    According to your description, you wan to calculate the differnce between the 2022 and 2023 if have the same PriceID , this is my test data and my understand for your description :

     

    Here are the steps you can refer to .

    We can click "New Measure" to create a measure like this:

    Measure = var _year = MAX('Table'[Year])
var _priceid= MAX('Table'[Price ID])
var _same = FILTER(ALLSELECTED('Table') , 'Table'[Price ID] = _priceid && 'Table'[Year] =_year-1)
var _cur_year = SUM('Table'[Unitary Price FC])
var _last_year = SUMX(_same , [Unitary Price FC])
return
IF(COUNTROWS(_same)>0,_cur_year-_last_year)

     

    Then we can put the fields we need on the visual and we can meet your need:

     

     

     

     

    If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem. (You can also upload you sample .pbix [without sensitive data] to the OneDrive and share with the OneDrive link to me ! )

    Thank you for your time and sharing, and thank you for your support and understanding of PowerBI! 

     

    Best Regards,

    Aniya Zhang

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly