Forum Discussion
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_AppraisalYear | CMMI_PROCESS_AREA | CC_CodeType | CommonFeature | AverageRating | YearDiff |
| 2022 | Enterprise Architecture Plan | Generic - Goal | C2P | 3.2 | 1.8 |
| 2022 | Enterprise Architecture Plan | Generic - Goal | A2P | 2.6 | 2.4 |
| 2022 | Enterprise Architecture Plan | Generic - Goal | AP | 1.8 | 3.2 |
| 2022 | Enterprise Architecture Plan | Generic - Goal | M&A | 1.3 | 1.7 |
| 2022 | Enterprise Architecture Plan | Generic - Goal | VI | 0.2 | 0 |
| 2022 | Enterprise Architecture Management | Generic - Goal | C2P | 5 | 0 |
| 2022 | Enterprise Architecture Management | Generic - Goal | A2P | 3.8 | -1.2 |
| 2022 | Enterprise Architecture Management | Generic - Goal | AP | 1.2 | 0.6 |
| 2022 | Enterprise Architecture Management | Generic - Goal | M&A | 0.2 | 0 |
| 2022 | Enterprise Architecture Management | Generic - Goal | VI | 0 | 0 |
| 2022 | Enterprise Architecture Plan | Specific - Level | C2P | 0 | 0 |
| 2022 | Enterprise Architecture Plan | Specific - Level | A2P | 0 | 0 |
| 2022 | Enterprise Architecture Plan | Specific - Level | AP | 0 | 0 |
| 2022 | Enterprise Architecture Plan | Specific - Level | M&A | 0 | 0 |
| 2022 | Enterprise Architecture Plan | Specific - Level | VI | 0 | 0 |
| 2022 | Enterprise Architecture Management | Specific - Level | C2P | 0 | 0 |
| 2022 | Enterprise Architecture Management | Specific - Level | A2P | 0 | 0 |
| 2022 | Enterprise Architecture Management | Specific - Level | AP | 0 | 0 |
| 2022 | Enterprise Architecture Management | Specific - Level | M&A | 0 | 0 |
| 2022 | Enterprise Architecture Management | Specific - Level | VI | 0 | 0 |
| 2023 | Enterprise Architecture Plan | Generic - Goal | C2P | 5 | null |
| 2023 | Enterprise Architecture Plan | Generic - Goal | A2P | 5 | null |
| 2023 | Enterprise Architecture Plan | Generic - Goal | AP | 5 | null |
| 2023 | Enterprise Architecture Plan | Generic - Goal | M&A | 3 | null |
| 2023 | Enterprise Architecture Plan | Generic - Goal | VI | 0.2 | null |
| 2023 | Enterprise Architecture Management | Generic - Goal | C2P | 5 | null |
| 2023 | Enterprise Architecture Management | Generic - Goal | A2P | 2.6 | null |
| 2023 | Enterprise Architecture Management | Generic - Goal | AP | 1.8 | null |
| 2023 | Enterprise Architecture Management | Generic - Goal | M&A | 0.2 | null |
| 2023 | Enterprise Architecture Management | Generic - Goal | VI | 0 | null |
| 2023 | Enterprise Architecture Plan | Specific - Level | C2P | 0 | null |
| 2023 | Enterprise Architecture Plan | Specific - Level | A2P | 0 | null |
| 2023 | Enterprise Architecture Plan | Specific - Level | AP | 0 | null |
| 2023 | Enterprise Architecture Plan | Specific - Level | M&A | 0 | null |
| 2023 | Enterprise Architecture Plan | Specific - Level | VI | 0 | null |
| 2023 | Enterprise Architecture Management | Specific - Level | C2P | 0 | null |
| 2023 | Enterprise Architecture Management | Specific - Level | A2P | 0 | null |
| 2023 | Enterprise Architecture Management | Specific - Level | AP | 0 | null |
| 2023 | Enterprise Architecture Management | Specific - Level | M&A | 0 | null |
| 2023 | Enterprise Architecture Management | Specific - Level | VI | 0 | null |
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 🙂
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.
9 Replies
Hi Anonymous ,
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.
Hi,
Based on the data that you have shared, show the expected result.
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.
Thank you so much, Ashish_Mathur 🙂
You are welcome.
