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 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:
Solved! Go to Solution.
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
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
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 |
---|---|
105 | |
102 | |
79 | |
73 | |
65 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |