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
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 🙂
Solved! Go to Solution.
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.
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.
You are welcome.
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.
Hi,
Based on the data that you have shared, show the expected result.
Thanks - I have amended my post, above 🙂
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.
Proud to be a Super User!
Thanks - I have amended my post, above 🙂
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
112 | |
105 | |
95 | |
58 |
User | Count |
---|---|
174 | |
147 | |
136 | |
102 | |
82 |