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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
appelture
Frequent Visitor

Calculate difference between rows in a single column based on multiple other columns

Hi, 

 

I have a table of data as follows, where I need to calculate the difference in AverageRating by CC_AppraisalYear, but also by the other columns i.e. calculate the difference in AverageRating for CMMI_PROCESS_AREA, CC_CodeType and CommonFeature between the later year and the earlier year (the CC_AppraisalYear). The "YearDiff" column shows my manual workings of how the calc should operate:

 

CC_AppraisalYearCMMI_PROCESS_AREACC_CodeTypeCommonFeatureAverageRatingYearDiff
2022Enterprise Architecture PlanGeneric - GoalC2P3.21.8
2022Enterprise Architecture PlanGeneric - GoalA2P2.62.4
2022Enterprise Architecture PlanGeneric - GoalAP1.83.2
2022Enterprise Architecture PlanGeneric - GoalM&A1.31.7
2022Enterprise Architecture PlanGeneric - GoalVI0.20
2022Enterprise Architecture ManagementGeneric - GoalC2P50
2022Enterprise Architecture ManagementGeneric - GoalA2P3.8-1.2
2022Enterprise Architecture ManagementGeneric - GoalAP1.20.6
2022Enterprise Architecture ManagementGeneric - GoalM&A0.20
2022Enterprise Architecture ManagementGeneric - GoalVI00
2022Enterprise Architecture PlanSpecific - LevelC2P00
2022Enterprise Architecture PlanSpecific - LevelA2P00
2022Enterprise Architecture PlanSpecific - LevelAP00
2022Enterprise Architecture PlanSpecific - LevelM&A00
2022Enterprise Architecture PlanSpecific - LevelVI00
2022Enterprise Architecture ManagementSpecific - LevelC2P00
2022Enterprise Architecture ManagementSpecific - LevelA2P00
2022Enterprise Architecture ManagementSpecific - LevelAP00
2022Enterprise Architecture ManagementSpecific - LevelM&A00
2022Enterprise Architecture ManagementSpecific - LevelVI00
2023Enterprise Architecture PlanGeneric - GoalC2P5null
2023Enterprise Architecture PlanGeneric - GoalA2P5null
2023Enterprise Architecture PlanGeneric - GoalAP5null
2023Enterprise Architecture PlanGeneric - GoalM&A3null
2023Enterprise Architecture PlanGeneric - GoalVI0.2null
2023Enterprise Architecture ManagementGeneric - GoalC2P5null
2023Enterprise Architecture ManagementGeneric - GoalA2P2.6null
2023Enterprise Architecture ManagementGeneric - GoalAP1.8null
2023Enterprise Architecture ManagementGeneric - GoalM&A0.2null
2023Enterprise Architecture ManagementGeneric - GoalVI0null
2023Enterprise Architecture PlanSpecific - LevelC2P0null
2023Enterprise Architecture PlanSpecific - LevelA2P0null
2023Enterprise Architecture PlanSpecific - LevelAP0null
2023Enterprise Architecture PlanSpecific - LevelM&A0null
2023Enterprise Architecture PlanSpecific - LevelVI0null
2023Enterprise Architecture ManagementSpecific - LevelC2P0null
2023Enterprise Architecture ManagementSpecific - LevelA2P0null
2023Enterprise Architecture ManagementSpecific - LevelAP0null
2023Enterprise Architecture ManagementSpecific - LevelM&A0null
2023Enterprise Architecture ManagementSpecific - LevelVI0null

 

So, for 2002, Enterprise Architecture Plan, Generic - Goal, C2P the YearDiff is 5 - 3.2 = 1.8 . The YearDiff for exactly the same row from 2023 is null as we don't yet have data from 2024.

 

I would love to be able to put in some of my attempts but they have all failed spectacularly! I am on the verge of splitting the table by CC_AppraisalYear and then doing a Merge on CMMI_PROCESS_AREA, CC_CodeType and CommonFeature to get data into a single row, from where I could calculate the difference between the AverageRating columns?!

 

Any help would be so appreciated - and whether PowerQuery or DAX would be the better option? Could the calculation be dyamic if I add additional years into CC_AppraisalYear and then use a filter to do the calcation on page? Thanks so much 🙂

1 ACCEPTED SOLUTION
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Yeardiff = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))=0,BLANK(),CALCULATE(MIN(Data[AverageRating]),FILTER(Data,Data[CMMI_PROCESS_AREA]=EARLIER(Data[CMMI_PROCESS_AREA])&&Data[CC_CodeType]=EARLIER(Data[CC_CodeType])&&Data[CommonFeature]=EARLIER(Data[CommonFeature])&&Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))-Data[AverageRating])

Hope this helps.

Ashish_Mathur_0-1691713432131.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

View solution in original post

9 REPLIES 9
Ashish_Mathur
Super User
Super User

Hi,

This calculated column formula works

Yeardiff = if(CALCULATE(COUNTROWS(Data),FILTER(Data,Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))=0,BLANK(),CALCULATE(MIN(Data[AverageRating]),FILTER(Data,Data[CMMI_PROCESS_AREA]=EARLIER(Data[CMMI_PROCESS_AREA])&&Data[CC_CodeType]=EARLIER(Data[CC_CodeType])&&Data[CommonFeature]=EARLIER(Data[CommonFeature])&&Data[CC_AppraisalYear]=EARLIER(Data[CC_AppraisalYear])+1))-Data[AverageRating])

Hope this helps.

Ashish_Mathur_0-1691713432131.png

 


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thank you so much, @Ashish_Mathur 🙂

You are welcome.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi again @Ashish_Mathur, I hope you might be able to provide some additional help on this one. Everything is working perfectly - I can see the yeardiff against the 'previous' years row. Is there a way to reflect this in the opposite? So, to see the yeardiff against the later year instead? i.e. yeardiff against a row for 2023 instead of 2022 (showing the increase/decrease of AverageRating exactly as before)? Any additional assistance would be so appreciated 🙂

Hi,

What result do you get when you change +1 in the formula to -1.  If it does not help, then share the data and show the expected result very clearly.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
Ashish_Mathur
Super User
Super User

Hi,

Based on the data that you have shared, show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Thanks - I have amended my post, above 🙂

danextian
Super User
Super User

Hi @appelture ,

 

What do you mean by calculate the difference in AverageRating? Using your data, please provide a sample formula of how that would be calculated. For example, sum of average rating for CMMI_PROCESS_AREA for 2022 vs 2023. Also, please provide  a data that includes both years.










Did I answer your question? Mark my post as a solution!


Proud to be a Super User!









"Tell me and I’ll forget; show me and I may remember; involve me and I’ll understand."
Need Power BI consultation, get in touch with me on LinkedIn or hire me on UpWork.
Learn with me on YouTube @DAXJutsu or follow my page on Facebook @DAXJutsuPBI.

Thanks - I have amended my post, above 🙂

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

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