Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
David23
Frequent Visitor

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

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

ok. very Big thanks for you help.

 

View solution in original post

11 REPLIES 11
1Daniel993
Helper III
Helper III

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

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

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

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

 

David23_0-1671057577201.png

 

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.






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

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.






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

 

David23_0-1671128917065.png

 

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.






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

 

 

ok. very Big thanks for you help.

 

Helpful resources

Announcements
Sept PBI Carousel

Power BI Monthly Update - September 2024

Check out the September 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

Sept NL Carousel

Fabric Community Update - September 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors