Forum Discussion
Calculate % difference between two years
I would like to work out % difference between 2 years for a set of values and having some trouble doing so.
First of all, let’s say I have the following sample of data
| Year | Fruit | Name |
2020 | Apple | John |
| 2019 | Apple | Mary |
| 2020 | Banana | Paul |
| 2020 | Orange | John |
| 2019 | Apple | Mary |
| 2019 | Banana | John |
| 2020 | Lemon | Paul |
| 2019 | Apple | Paul |
In a visual, I want to create the following Matrix.
| Apple | Banana | |||||
| Name | 2019 | 2020 | % change | 2019 | 2020 | % change |
| John | 23% | 19% | -4% | 22% | 16% | -6% |
| Paul | 22% | 19% | -3% | 13% | 21% | 8% |
| Mary | 24% | 22% | -2% | 12% | 26% | 14% |
The year % is easy, im just struggling to correctly work out the values in the % change for each person using a measure
Any assistance would be greatly appreciated
Regards,
Cal
SuperCal99 , You need create a separate year table(say Date) and the create a measure like
example
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])
3 Replies
SuperCal99 , You need create a separate year table(say Date) and the create a measure like
example
This Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])))
Last Year = CALCULATE(sum('Table'[Qty]),filter(ALL('Date'),'Date'[Year]=max('Date'[Year])-1))diff = [This Year]-[Last Year ]
diff % = divide([This Year]-[Last Year ],[Last Year ])Thanks amitchandak - appreciate that
Hi SuperCal99 ,
You can refer to the following link to try out grouping in a custom matrix:
https://community.powerbi.com/t5/Desktop/Matrix-column-header-label-custom-group/td-p/1253196
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
