Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago

Adjust matrix table - hide unnecessary column

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...
Need Help
Anonymous's avatar
Anonymous
Not applicable
2 years ago

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's avatar
    Anonymous
    Not applicable
    2 years ago

    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 MonthCost typeValueCost Center adjProduct LineService LineBGT/ACT
    01.07.2024Revenue20000CC 1PL 1FIBGT
    01.07.2024Personnel costs-15000CC 1PL 1FIBGT
    01.07.2024Other costs-1000CC 1PL 1FIBGT
    01.07.2024Travel expenses-200CC 1PL 1FIBGT
    01.07.2024Learning & Training-300CC 1PL 1FIBGT
    01.07.2024Office costs-2000CC 1PL 1FIBGT
    01.07.2024Revenue20600CC 1PL 1FIACT
    01.07.2024Personnel costs-15450CC 1PL 1FIACT
    01.07.2024Other costs-1030CC 1PL 1FIACT
    01.07.2024Travel expenses-206CC 1PL 1FIACT
    01.07.2024Learning & Training-309CC 1PL 1FIACT
    01.07.2024Office costs-2060CC 1PL 1FIACT

     

    I would really appreciate any support as I got stuck and I don't know how to proceed further.

    Thank you! 🙂

    • Anonymous's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.