Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

dynamic conditinal formatting based on mtrix condition

Hi there,

When you have a martix with Actuals and Budget columns the precentages and color coding make sense when the matrix colapsed but when you expand the matrix it dosen't make sense to have color coding because you can't compare line item to the overall budget (Let say office supply budget for the month is 1000 but when I expand I have 10 items 100 each so I can't compare line item to the budget)

My question how to make the color coding appear when the matrix is collapse and invisible when it expand?

Thanks,

Oded Dror

Tips and Tricks
  • Anonymous's avatar
    Anonymous
    7 years ago

    Hi there,

     

    This one works and I did conditional formatting based on that

     

    Formatting =
    VAR Percentage = [Actual Vs Budget]
    VAR CheckExpand =  Not(HASONEVALUE(GLedger[Month Name])) ||
        Not(HASONEVALUE(GLedger[GL_ACCT_TYPE])) || NOT(HASONEVALUE(GLedger[DD]))
    RETURN
    If(CheckExpand = TRUE(),
       Percentage,0)
    Thanks
    Oded Dror
  • MFelix's avatar
    MFelix
    7 years ago

    Hi Anonymous ,

     

    Glad I could give some pointers to get the solution don't forget to mark your result as the answer for this post to help others.

     

    Regards,

    MFelix

13 Replies

  • MFelix's avatar
    MFelix
    Super User
    7 years ago
    Hi Anonymous,

    You should try something like

    Conditional = IF(HASONEFILTER( TABLE[Column]) ; 0 ;1)

    Then make the conditional formatting based on the 1.

    You can also replace the 1 by the hex coding and then you just need to set it in the conditional formatting.

    Regards,
    MFelix
    • Anonymous's avatar
      Anonymous
      Not applicable
      7 years ago

      Hi MFlix,

       

      I tried that but it still show conditional formatting when the matrix is expanded.

      One thing my conditional colun is based on other column (Precent value) and adding another column make it impossible to achive.

      If it was ability to do like SSRS when you can change the background colore based on condition with IF satement then your solution was apply.

       

      Thanks,

      Oded Dror

      • MFelix's avatar
        MFelix
        Super User
        7 years ago

        Hi Anonymous ,

         

        I made a small example with the following setup:

         

        Cat SubCat Value
        A A 100
        A B 200
        B A 100
        B B 100
        C A 20
        C B 50

         

        Created the following measure:

        Formatting = IF(NOT(HASONEVALUE('Table'[SubCat]) );"red")

        Then made the condittional formating on values:

         

        Two remarks:

        • Use the NOT so that the expanded would not need to have the hex coding
        • You can change the "Red" by the HEX code something like "#FF0000"

         

        Be aware that when you expand one of the categories on the matrix table the condittional formatting will not aplly to any subtotals (this is behaviour on PBI not error).

         

        Regards,

        MFelix