Reply
David23
Frequent Visitor
Partially syndicated - Outbound

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

 

David23_0-1671032527620.png

 

any idea how i can do this please?

thanks in davance

2 ACCEPTED SOLUTIONS

Syndicated - Outbound

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Syndicated - Outbound

ok. very Big thanks for you help.

 

View solution in original post

11 REPLIES 11
1Daniel993
Helper III
Helper III

Syndicated - Outbound

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

1Daniel993_0-1671031994926.png

 

Regards

Syndicated - Outbound

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

Syndicated - Outbound

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

Syndicated - Outbound

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

 

David23_0-1671057577201.png

 

Syndicated - Outbound

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

result.jpg

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"
        )
    )
)

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Syndicated - Outbound

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

Syndicated - Outbound

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

CF.gif

 

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

Model.jpg

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:

CF.gif

Sample PBIX file attached





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Syndicated - Outbound

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

 

David23_0-1671128917065.png

 

Syndicated - Outbound

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 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Syndicated - Outbound

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

 

David23_0-1671442467146.png

 

 

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"
            )
        )
    )

 

 

Syndicated - Outbound

ok. very Big thanks for you help.

 

avatar user

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

ArunFabCon

Microsoft Fabric Community Conference 2025

Arun Ulag shares exciting details about the Microsoft Fabric Conference 2025, which will be held in Las Vegas, NV.

December 2024

A Year in Review - December 2024

Find out what content was popular in the Fabric community during 2024.

Top Solution Authors (Last Month)
Top Kudoed Authors (Last Month)