Calculate difference between columns for matrix table with few levels of column header

[weibing86]

I have the following sets of data:

FY	ACT/BUD	Business Unit	GL Category	Value
2023	ACT	U1	Sales	1,000.00
2023	ACT	U1	Cost	(300.00)
2023	ACT	U1	Profit	700.00
2023	BUD	U1	Sales	500.00
2023	BUD	U1	Cost	(100.00)
2023	BUD	U1	Profit	400.00
2023	ACT	U2	Sales	700.00
2023	ACT	U2	Cost	(200.00)
2023	ACT	U2	Profit	500.00
2023	BUD	U2	Sales	800.00
2023	BUD	U2	Cost	(500.00)
2023	BUD	U2	Profit	300.00
2023	ACT	U3	Sales	400.00
2023	ACT	U3	Cost	(200.00)
2023	ACT	U3	Profit	200.00
2023	BUD	U3	Sales	600.00
2023	BUD	U3	Cost	(500.00)
2023	BUD	U3	Profit	100.00

After converting the data into matrix table, I would like to insert columns showing difference between ACT and BUD for different business units as follows:

	2023	2023	2023	2023	2023	2023	2023	2023	2023
	ACT	ACT	ACT	BUD	BUD	BUD	DIFF	DIFF	DIFF
	U1	U2	U3	U1	U2	U3	U1	U2	U3
Sales	1,000.00	700.00	400.00	500.00	800.00	600.00	500.00	(100.00)	(200.00)
Cost	(300.00)	(200.00)	(200.00)	(100.00)	(500.00)	(500.00)	(200.00)	300.00	300.00
Profit	700.00	500.00	200.00	400.00	300.00	100.00	300.00	200.00	100.00

Can someone enlighten on how to insert the columns showing difference?

[weibing86]

Hi guys, thanks for your help.

Actually my scenario is even more completed as I have slicer which allows me to choose different paramets, eg FY, BU, entity, so my matrix table is dynamic.

Is there a way to directly compute the difference between columns based on the table generated after selecting desired parameters in slicer?

[danextian]

Hi @weibing86 

Have you opened my sample pbix and played with it? Which part doesnt work for you? I would suggest you posted a sample data that covered your use case and further expounded on it instead of just bits and pieces. and

[weibing86]

Hi i have finall understood your method.

First, you created a new measure DIFF, which is difference between the 2 measures ACT and BUD. Then split this DIFF to different parameters (e.g. FY, business unit etc). This works for me.

However, when i used your 2nd method which is the 'switch' measure, only ACT and BUD showed up; the DIFF columns do not show up. I have followed exactly the steps in your file, is there any step which i may have missed?

[weibing86]

XX

[danextian · Accepted solution]

Hi @weibing86,

The look you are trying to achieve is not achievable by simply creating measures to calculate for Actual, Budget and the difference. Here's how that would look using that approach.

Notice that the measures are below the business units instead of other way around.

To achieve what you want, you need to create a disconnected table that has a column of the name of the measures and another measure that will return the respective actual measures depending on the value of the said column. That approach would yield this:

Take note that the matrix visual does not repeat column headers so you will see just one ACT/BUD/DIFF across all business units.

Please see attached pbix for reference.

[sanalytics]

Hello @weibing86 

Create a Disconnected table and used that table as column in Matrix section

Create DAX to show the difference.

Below is the screenshot

Below is the link of complete solution of pbix file

https://dropmefiles.com/K4aE6

Regards

sanalytics

If it is your solution then please like and accept it as solution