Conditional formatting

[milkynight]

Hi everyone,

Appreciate help and guidance for conditional formatting with the below context. I have tried several ways but the result so far is not correct.

Below table is the number of units by store level in a weekly format. Data is imported from Excel file and not from a measure or calculated column.

I would like to highlight all the value that is higher than the average units of Feb23 W1 and W2.

For example:

Active Supermart - average FebW1 and FebW2 = 8771+8743 = 8757 => any value that is higher than 8757 will be highlighted

Thanks much for your help!

[milkynight]

Hi @Anonymous , many thanks for the detailed guidance!

However, some issues i encounterd: 

Point 3 - there is no Advanced Controls button in my PBI version. Am I using old version and upgrade required?

I turned ON for Font Color and a window popped up with little fields compared to your guidance:

Much appreciate your guidance on this!

[Anonymous]

HI @milkynight ,

try below steps

• Select the table visual that you want to apply conditional formatting to.

• In the "Visualizations" pane, navigate to the "Conditional formatting" section.

• Click on the "Advanced controls" button (represented by a three-dot icon) next to "Color scale" or "Data bars" options, depending on your preference.

• In the advanced controls, select "Add rule" and choose the "Field value" option.

• In the "By field" dropdown, select the field that contains the units data for each store level (e.g., "Units").

• In the "Based on field" dropdown, select the field that represents the week (e.g., "Week").

• In the "Format by" dropdown, select "Greater than".

• In the "Based on field value" dropdown, select "Average".

• In the "By value" dropdown, select "All values in column".

• In the "Value" dropdown, enter the DAX expression to calculate the average of units for Feb23 W1 and W2. Assuming your field for units is named "Units" and the field for week is named "Week", the expression would be:

  less
• AVERAGEX(FILTER('YourTable', 'YourTable'[Week] IN {"Feb23 W1", "Feb23 W2"}), 'YourTable'[Units])

• Choose the desired formatting options, such as the color or data bar style, to highlight the values that are higher than the calculated average.

• Click "OK" to apply the conditional formatting.