Forum Discussion

NickHoffmans's avatar
NickHoffmans
Frequent Visitor
4 years ago

Separate conditional formatting on matrix subtotals and values

Hello, 
I have a matrix in which I want to apply to both the (sub)totals as well as on the values below. However, I would like to have the conditonal formatting applied separated from each other, in order to keep the (way higher) values from the (sub)totals out of the formatting of the values.

However, I don't see the possibility to have two distinct conditonal formats if I want both to be formatted in the same way (e.g., font color).

How can I make this happen?

 

Thanks!

Need Help
Tips and Tricks

8 Replies

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    Can you provide some details with screenshots or with something else , what you want 

    • NickHoffmans's avatar
      NickHoffmans
      Frequent Visitor
      4 years ago

      Sure.

      I do have two columns (turnover and costs) which I subtract to a third column: margin

      Here, I put the conditional formatting on the "values"-level of 'margin'.

      When putting it on "totals"-level of 'margin'

       

      I woud like to have it applied to both the values and the totals together, but separately, since the "totals" are (obviously) way higher than the "values".

      • v-chenwuz-msft's avatar
        v-chenwuz-msft
        Icon for Community Support rankCommunity Support
        4 years ago

        Hi NickHoffmans,

         

        It seems that it can not set the total color under conditional formatting. Only the Subtotals can set the total color without rules.

        You can present an idea on the power bi ideas .

         

        If I am wrong, please let me know. If you can set total font dynamically, some measures like the following  maybe work.

        Measure =
VAR _s =
    SUM( 'Table'[Values] )
VAR _value =
    SWITCH(
        TRUE(),
        _s < 20, "red",
        _s >= 20
            && _s < 40, "yellow",
        _s >= 40, "green"
    )
VAR _total =
    SWITCH(
        TRUE(),
        _s < 100, "Purple",
        _s >= 100
            && _s < 300, "Orange",
        _s >= 300, "blue"
    )
RETURN
    IF( HASONEVALUE( 'Table'[Values] ), _value, _total )

         

        Best Regards

        Community Support Team _ chenwu zhu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • mh2587's avatar
    mh2587
    Icon for Super User rankSuper User
    4 years ago

    There will be option "Apply to " in this select both the values and total option I hope this will help you

    • NickHoffmans's avatar
      NickHoffmans
      Frequent Visitor
      4 years ago

      I am aware of the "apply to"-button. However, if click "Values and totals" it will count them together.

      However, I would like to have two separate ranges (one range for "Values", one range for "Totals") to apply the different conditonal formats to.

      • mh2587's avatar
        mh2587
        Icon for Super User rankSuper User
        4 years ago

        Try to handle this with measure Just define the ranges and color codes in switch function may be this logic will resolve the issue