Forum Discussion
Adjust matrix table - hide unnecessary column
Hi, Anonymous
Is it possible to provide some of the example data? Do you expect the data to result in hiding the red column? I can place your desired output by calculation on the far right side of the table. This eliminates the need to add the measure [Diff%].
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Yes, I would like to hide the red column as the same result is actually visible twice in the table.
The idea is to have the comparison of ACT and BGT values for each Service Line and Product Line per cost type.
| Reporting Month | Cost type | Value | Cost Center adj | Product Line | Service Line | BGT/ACT |
| 01.07.2024 | Revenue | 20000 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Personnel costs | -15000 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Other costs | -1000 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Travel expenses | -200 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Learning & Training | -300 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Office costs | -2000 | CC 1 | PL 1 | FI | BGT |
| 01.07.2024 | Revenue | 20600 | CC 1 | PL 1 | FI | ACT |
| 01.07.2024 | Personnel costs | -15450 | CC 1 | PL 1 | FI | ACT |
| 01.07.2024 | Other costs | -1030 | CC 1 | PL 1 | FI | ACT |
| 01.07.2024 | Travel expenses | -206 | CC 1 | PL 1 | FI | ACT |
| 01.07.2024 | Learning & Training | -309 | CC 1 | PL 1 | FI | ACT |
| 01.07.2024 | Office costs | -2060 | CC 1 | PL 1 | FI | ACT |
I would really appreciate any support as I got stuck and I don't know how to proceed further.
Thank you! 🙂
Hi, Anonymous
You can try the following methods.
Measure = VAR _Bgt=CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Cost type]),[BGT/ACT]="BGT")) VAR _Act=CALCULATE(SUM('Table'[Value]),FILTER(ALLEXCEPT('Table','Table'[Cost type]),[BGT/ACT]="ACT")) VAR _Diff=DIVIDE(_Bgt-_Act,_Bgt) RETURN IF(HASONEVALUE('Table'[BGT/ACT]),SELECTEDVALUE('Table'[Value]),_Diff)Is this the result you expected?
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
This is exactly what I need! 🙂 Looks perfect!
I tried to copy your solution to my PBI and unfortunately, I don't see neither all Cost Types nor columns "BGT"/"ACT" in the matrix.
Do you know what might be causing the issue? I do have some rows empty in my table - I mean that they have no value.
Hi, Anonymous
Try select the row and right-click，choose Show items with no data.
Best Regards,
Community Support Team _Charlotte
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.