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 

 

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

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

      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: