Need Help in Conditional Formating in BI

[Dhrutivyasa-070]

I need to apply conditional formating in BI -as per shown in below image 

location wise month wise (each row wise ) highest & lowest value . 

highest value for each location & each month should be green. 

i have tried calcuation using summarize coulms but  its not working . please help with the dax 

Refrence snapshot for problem statement 

Thanks in Advance.

[Jihwan_Kim · Accepted solution]

Hi,

Please check the below picture and the attached pbix file.

Sales measure: = 
SUM(Sales[Sales])

INDEX function (DAX) - DAX | Microsoft Learn

Color format measure: = 
VAR _sales = [Sales measure:]
VAR _hightest =
    CALCULATE (
        [Sales measure:],
        INDEX (
            1,
            ALL ( 'Calendar'[Month-Year sort], 'Calendar'[Month-Year] ),
            ORDERBY ( [Sales measure:], DESC )
        )
    )
VAR _lowest =
    CALCULATE (
        [Sales measure:],
        INDEX (
            1,
            ALL ( 'Calendar'[Month-Year sort], 'Calendar'[Month-Year] ),
            ORDERBY ( [Sales measure:], ASC )
        )
    )
RETURN
    SWITCH ( TRUE (), _sales = _hightest, "green", _sales = _lowest, "red" )

[123abc]

To apply conditional formatting in Power BI to highlight the highest and lowest values for each location and each month, you can follow these steps:

1. Create a new calculated column to identify the highest and lowest values for each row.
2. Apply conditional formatting based on this calculated column.

Here's how you can achieve this using DAX:

1. Create a Calculated Column:

You need to create a calculated column that checks if the current value is the highest or lowest for the respective location and month.

MaxValue =
VAR CurrentLocation = 'YourTable'[Location]
VAR CurrentMonth = SELECTEDVALUE('YourTable'[Month])
RETURN
IF(
'YourTable'[Value] = CALCULATE(MAX('YourTable'[Value]), ALLEXCEPT('YourTable', 'YourTable'[Location], 'YourTable'[Month])),
"Max",
IF(
'YourTable'[Value] = CALCULATE(MIN('YourTable'[Value]), ALLEXCEPT('YourTable', 'YourTable'[Location], 'YourTable'[Month])),
"Min",
BLANK()
)
)

Replace 'YourTable' with the name of your table, and 'Value' with the name of the column that contains your numerical values.

1. Apply Conditional Formatting:

   • Go to the table or matrix visualization where you want to apply conditional formatting.
   • Select the column (e.g., APR, MAY, JUN, etc.) where you want to apply conditional formatting.
   • Go to the "Conditional formatting" option in the formatting pane.
   • Choose "Background color" and select "Rules" -> "Field value" -> "MaxValue".
   • Set the desired color for the highest values.
   • Repeat the process to set the formatting for the lowest values.

This setup will highlight the highest and lowest values for each location and month in your Power BI report. Make sure you adjust column names and table names according to your data model.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.