Alternate matrix row colors

[David23]

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 lines of zone 2 and 4 with a yellow background color.

any idea how i can do this please?

thanks in davance

[PaulDBrown · Accepted solution]

Sorry, I don't have Report Server installed, so I cannot test it. Maybe try on the Report Server forum?

https://community.powerbi.com/t5/Report-Server/bd-p/ReportServer 

[1Daniel993]

Hello you need go to the field select the Conditional background and in Format style Select Rules, and just put your requirements

Regards

[David23]

I just wish alternate matrix row colors .
 Conditional background does not allow colors to be alternated.

[1Daniel993]

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

[David23]

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

[PaulDBrown]

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]

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]

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

[David23]

in my Power Bi Desktop RS, the CF variable is grayed out, I can't select it :

[PaulDBrown · Accepted solution]

Sorry, I don't have Report Server installed, so I cannot test it. Maybe try on the Report Server forum?

https://community.powerbi.com/t5/Report-Server/bd-p/ReportServer 

[David23]

I increased the measure in the conditional formatting. but the display is not at the top :

here is the script used :

Me_CF2 = 
VAR _Rnk =
    IF (
        ISBLANK ( [S-Revenue_actual] ),
        BLANK (),
        CALCULATE (
            RANKX ( ALLSELECTED ( DIM_PRODUCT ), [S-Revenue_actual],, DESC ),
            ALLEXCEPT ( DIM_PRODUCT, DIM_PRODUCT[pr_offer] )
        )
    )
RETURN
    IF (
        NOT ISINSCOPE ( DIM_PRODUCT[pr_offer] ),
        "White",
        IF (
            NOT ISINSCOPE ( Dim_Time[Period] ),
            "White",
            IF (
                AND ( ISINSCOPE ( DIM_PRODUCT[pr_offer] ), ISEVEN ( _Rnk ) ), 
                "#EFB5B9",
                "#A0D1FF"
            )
        )
    )

[David23 · Accepted solution]

ok. very Big thanks for you help.