Forum Discussion
Calculate difference between columns for matrix table with few levels of column header
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?
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.
6 Replies
Hello weibing86
Create a Disconnected table and used that table as column in Matrix sectionCreate DAX to show the difference.
Below is the screenshot
Below is the link of complete solution of pbix file
Regards
sanalytics
If it is your solution then please like and accept it as 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.
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?
