Adjust matrix table - hide unnecessary column

[Anonymous]

Dear All,

I have built a matrix table with data for actual and budgeted values per type. I also added a % comparison (how much ACT differs from BGT), but it is (obviously) showed twice - is there any option to hide one of the columns for example the one marked red as at the end, when the data is completed, they will show exactly the same values?

Not sure if it matters, but the layout of my data source looks like this:

The table is built with UNION as a summary of other table.

Any suggestions will be appreciated.

Thank you in advance!

[Anonymous]

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.

[Anonymous]

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! 🙂

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

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.

[Anonymous]

Thank you for the suggestion, but still no luck 😞

I focused on Revenue and I can see the total amounts as well as the measure calculated correctly, but for some reason the columns ACT and BGT are empty: