conditional formatting

[HAMZA_07]

Hello, I want help to color the first three rows with fixed colors, and the rest I want to use conditional formatting on them ....

as columns and not as rows

[MFelix]

Hi @HAMZA_07 ,

For this you need to do some advance editing of the condittional formating this also depends on how you want to format the rows below.

For the first 3 lines create a metric similar to this:

TOP ROWS FORMATTING = IF(SELECTEDVALUE('Table (2)'[Index]) <=3 , "#118DFF")

This need to be used on all your columns has condittional formatting.

For the Long and Short depends on how you want to do the conditional formatting if it's by the diverging colours you need to add the following two metrics for each column:

percentile long =
IF (
    SELECTEDVALUE ( 'Table (2)'[Index] ) > 3,
    CALCULATE (
        PERCENTILE.EXC ( 'Table (2)'[Long], 0.5 ),
        FILTER ( ALL ( 'Table (2)' ), 'Table (2)'[Index] > 3 )
    )
)


Percentile_Long_Variation =
IF (
    SELECTEDVALUE ( 'Table (2)'[Index] ) > 3,
    SUM ( 'Table (2)'[Long] ) - [percentile long]
)

Now use this has the condittional formatting of the long column:

Be aware that you need to select the specific colour for empty values

If the Long/Short has specific rules the you need to change the percentile to the specific rules using a swich statment:

I have created the following rule for short:

Format Short = 
SWITCH( TRUE(),
    SELECTEDVALUE ( 'Table (2)'[Index] ) <= 3, "#118DFF",
    SUM('Table (2)'[Short]) <= 11000, "Green",
    SUM('Table (2)'[Short]) <= 14000, "Red",
    SUM('Table (2)'[Short]) <= 99000, "BLue"
)

On the example above I only formated the Date , long short and net positions but you can do it for all the columns.

[HAMZA_07]

Thank you for your reply, but I did not understand how to apply the new metric... I want the first three rows to be like what you applied in a picture, but in yellow color... and the columns like the picture below

[MFelix · Accepted solution]

Hi @HAMZA_07 

On the columns Date, Net Positions, Long %, Short %, TOTAL and Index you apply the custom format has a value using the metric TOP Rows Format:

On the columns for the long / short where you want to have the divergent you apply the divergent but instead of select the value of the column you select the measure Percentile variation (short or long depending).

The blank values that are marked in my case has blue you need to set to yellow

[HAMZA_07]

Brilliant trick. Using a percentage column you made it read it's empty . thank you 😊

[MFelix]

Did my solution worked?

You marked your answer has the solution. Is that correct? 

[HAMZA_07]

Sorry I fixed it. Thank you

[MAwwad]

To color the first three rows with fixed colors and use conditional formatting on the rest of the rows in a Power BI table, you can use the following steps:

1. Create a new column in your table that assigns a fixed color to the first three rows and a default color to the rest. You can do this using a calculated column with a formula similar to the following:
   Copy code

   Fixed Color = IF ( RANKX ( ALL ( 'Table' ), [Column] ) <= 3, SWITCH ( RANKX ( ALL ( 'Table' ), [Column] ), 1, "#FF0000", 2, "#00FF00", 3, "#0000FF", "#FFFFFF" ), "#FFFFFF" )

   This formula uses the RANKX function to assign a rank to each row based on the value of the "Column" field, and then uses the SWITCH function to assign a fixed color to the first three ranks and a default color to the rest. You can adjust the colors as needed by modifying the values in the SWITCH function.

   1. Go to the "Format" tab for the table visual and click on the "Conditional formatting" button in the "Data colors" section.

   2. In the "Conditional formatting" window, select the "Field" that you want to use for the conditional formatting, and then set the "Format by" option to "Color scales".

   3. In the "Color scales" section, select the default color that you want to use for the rest of the rows.

   4. In the "Data colors" section, set the "Data colors" option to "Fixed" and then select the "Fixed color" column that you created in step 1.

   5. Click the "OK" button to apply the formatting to the table.