Forum Discussion

David23's avatar
David23
Frequent Visitor
3 years ago
Solved

Alternate matrix row colors

Hello I work with Power Bi Desktop RS. I want a dynamic background color. I explain myself with the image below: I want the lines of area 1, 3 and 5 to be with a red background color. and the li...
Need Help
1Daniel993's avatar
1Daniel993
Icon for Helper III rankHelper III
3 years ago

Can you send us one example that you want in excel?

David23's avatar
David23
Frequent Visitor
3 years ago

i want have dynamique alternate matrix row colors (orange/blue) like this :

 

 

  • PaulDBrown's avatar
    PaulDBrown
    Icon for Community Champion rankCommunity Champion
    3 years ago

    At present, you cannot conditional format row headers. So this is the closest I've got:

    Using a measure for the conditional formatting (& manual formatting of column and row totals) along the lines of:

    CF =
IF (
    NOT ISINSCOPE ( Data[Pr offer] ),
    "White",
    IF (
        NOT ISINSCOPE ( Data[Year - Month] ),
        "White",
        IF (
            SELECTEDVALUE ( Data[Pr offer] )
                IN { "Bi & Data", "Business Consulting", "Infrastructure" },
            "#EFB5B9",
            "#A0D1FF"
        )
    )
)

     

    • David23's avatar
      David23
      Frequent Visitor
      3 years ago

      Thank you for your reply. I have 2 question plz :

       

      1- how to assign CF measure to my matrix?

       

      2- the list  IN { "Bi & Data", "Business Consulting", "Infrastructure" } can grow or shrink depending on what is going to be in the database. is it possible to make this line of script dynamic?

       

      thanks in advance

      • PaulDBrown's avatar
        PaulDBrown
        Icon for Community Champion rankCommunity Champion
        3 years ago

        1. The CF measure is a conditional formatting measure, so add it to the measure in the matrix.

         

        2. First of all, I have set up dimension tables for the different fields.

        Instead of hardcoding the values, you can include a rank measure together with the ISEVEN function  to colour the even rows in one colour and the odd rows another colour in the CF measure as follows:

        CF =
VAR _Rnk =
    IF (
        ISBLANK ( [SUM Sales] ),
        BLANK (),
        CALCULATE (
            RANKX ( ALLSELECTED ( 'Pr Offer Table' ), [SUM Sales],, DESC ),
            ALLEXCEPT ( Data, 'Pr Offer Table'[Pr offer] )
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( 'Pr Offer Table'[Pr offer] ),
        "White",
        IF (
            NOT ISINSCOPE ( Data[Year - Month] ),
            "White",
            IF (
                AND ( ISINSCOPE ( 'Pr Offer Table'[Pr offer] ), ISEVEN ( _Rnk ) ),
                "#EFB5B9",
                "#A0D1FF"
            )
        )
    )

        Sort the matrix by sales in descending order and you will get:

        Sample PBIX file attached