Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
GRCzz
Regular Visitor

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

 

VDCODEyear total_amt 
A20152,444,251.50
A20164,061,466.82
A20171,305,996.76
B20161,095,057.60
B20171,349,614.27
B20181,441,647.36
B20191,447,807.68
B20201,385,420.64
B2021339,678.72
C20173,743,832.60
C20184,726,414.90
C20193,552,500.57
C20203,349,852.49
C2021886,912.76

 

Expected result 

 

GRCzz_1-1663671951872.png

 

thank you 

 

1 REPLY 1
amitchandak
Super User
Super User

@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/1...
https://community.powerbi.com/t5/Quick-Measures-Gallery/The-New-Hotness-Custom-Matrix-Hierarchy/m-p/...

vote for Hybrid Table
https://ideas.powerbi.com/ideas/idea/?ideaid=9bc32b23-1eb1-4e74-8b34-349887b37ebc

Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.