Forum Discussion
How to compare %change between two column
Dear Expert,
please kindly help with the detail below.
I need to compare the %change between the year and the previous year's total amount and separate it by VendorID in power bi desktop
my raw data as the table below
| VDCODE | year | total_amt |
| A | 2015 | 2,444,251.50 |
| A | 2016 | 4,061,466.82 |
| A | 2017 | 1,305,996.76 |
| B | 2016 | 1,095,057.60 |
| B | 2017 | 1,349,614.27 |
| B | 2018 | 1,441,647.36 |
| B | 2019 | 1,447,807.68 |
| B | 2020 | 1,385,420.64 |
| B | 2021 | 339,678.72 |
| C | 2017 | 3,743,832.60 |
| C | 2018 | 4,726,414.90 |
| C | 2019 | 3,552,500.57 |
| C | 2020 | 3,349,852.49 |
| C | 2021 | 886,912.76 |
Expected result
thank you
1 Reply
GRCzz , with help from separate year or date table you need measures like
//Only year vs Year, not a level below
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 ])for the above structure consider
Hybrid display with Matrix Column and measure
https://community.powerbi.com/t5/Community-Blog/Creating-a-custom-or-hybrid-matrix-in-PowerBI/ba-p/1354591
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/963588#M428vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc
