Consolidate rows and sum

[Godislove22222]

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

[Godislove22222]

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

[Godislove22222]

[v-xinruzhu-msft]

Hi @Godislove22222 

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

[Godislove22222]

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? 

[v-xinruzhu-msft · Accepted solution]

Hi @Godislove22222 

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

[v-xinruzhu-msft]

Hi @Godislove22222 

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.

[Godislove22222]

[Godislove22222]

Thank you!!!

My issues is I have some values with zeros that is resulting in an error. 

Please see attached is the file.