Forum Discussion
Consolidate rows and sum
Good day PowerBI Guru.
Newbe here.
My data has the following layout.
Goal: Remove "Line" by summing FY23, then FY 24 for each office.
Then caluate % Difference and have an icon or chart showing increase or decrease.
Data looks like this...
| Office | Line | FY2023 Plan | FY 2024 Plan | % Difference | % Difference Icon |
| Office A | Line 1 | 100 | 200 | ||
| Office A | Line 2 | 110 | 105 | ||
| Office B | Line 3 | 120 | 220 | ||
| Office C | Line 2 | 130 | 230 |
| Need this... | ||||
| FY2023 Plan | FY 2024 Plan | % Difference | Icon | |
| Office A | 210 | 305 | ||
| Office B | 120 | 220 | ||
| Office C | 130 | 230 |
Based on the data you have provided, the value of sox com in 2023 is 0.
so it will returen 0
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
8 Replies
Thank you!!!
My issues is I have some values with zeros that is resulting in an error.
Please see attached is the file.
1.You can create a measure.
Difference = DIVIDE(SUM('Table'[FY 2024 Plan])-SUM('Table'[FY2023 Plan]),SUM('Table'[FY2023 Plan]))2.Create a measure to set the icon.
Format = if([Difference]>0,1,0)3.Put the following field to a matrix visual.
4.Set the conditional formatting of the difference measure, select 'Icon'
5.Set the rule.
Output
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Do you mean that you want to add a new column in the table to calculate the difference?
If you want to achieve it, you can refer to the following calculated columns.
Sum_2023 = SUMX(FILTER('Table',[CATEGORY]=EARLIER('Table'[CATEGORY])),[2023 PLAN])Sum_2024 = SUMX(FILTER('Table',[CATEGORY]=EARLIER('Table'[CATEGORY])),[2024 Plan])%Difference = DIVIDE([Sum_2024]-[Sum_2023],[Sum_2023])Output
If you want to implement it by using measure, you just put the CATEGORY to the row field to the matrix, and put the 2023 plan and 2024 plan to the value, it will automatic aggregate. then follow the soution I offered at 4th message to create the measure.
Best Regards!
Yolo Zhu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
When I add the % Difference column to what you provided, I get the following. Is this correct? And lastly, the "SOX COM" had a zero plan in FY23 but a positive amount in FY24. Any suggestions for this one?
CATEGORY LINE 2023 PLAN 2024 Plan MONEY 3V 134964 112500 ETRAVEL 3V 101089 75817 ORACLE 3V 1798866 884543 PRINTING 34 3060000 2718046 PRINTING 36 0 6500000 PRINTING 31 0 500000 FUEL 3T -24463600 -15529604 SMART P 3T -723025 -991778 REVENUE 36 0 2344350 REVENUE 3V 207006 100000 SOX COM 36 0 9430460 ACCOUNT 46 0 300000 ACCOUNT 3V 1037479 1106125 OTHER R 3V 2171230 2099101 PAYROLL 3V 1357019 1145400 FUND 3V 45234 41500 TRAVEL 3V 119842 96880 TRAVEL 34 15860 16000 RPA 46 89417 91300 EMIN 36 285000 8047817 AUDIT T 6W 0 2500 AUDIT T 3V 58805 104580 CLAIMS 34 7019 50000 CLAIMS 3V 759026 993144 TIMEATD 3V 1266551 1162000
