Compare Monthly Totals to Calculated Average and Highlight Values Above using Conditional Formating

[AJW2023]

I am trying to use a DAX command to identify monthly totals that are above the monthly average and use conditional formating to highlight those which are above the average.  Current commands used:

- Total = SUM(sheet1[Number Sold])

- Average = AVERAGEX(VALUES(Sheet1[Date]),[Total])

Have attempted to use:

- Measure = IF([Total]>[Average], "Red", "Green") 

However I am not getting the desired result and it does not identify the months where the total is above the monthly average per category.

[Jihwan_Kim]

Hi,

I am not sure how your data model looks like, but please try to fix the AVERAGE MEASURE something like below whether it suits your requirement.

Average measure: =
AVERAGEX ( ALLSELECTED ( Sheet1[monthname], Sheet1[monthname_sort] ), [Total] )

[AJW2023]

@Jihwan_Kim  that worked to get me the correct average calulation however it is still not working for the measure comparing the monthly totals per category to the average to identify those which are above the average.

For example from the original screen shot, for "Site A" the average is 3.67 therefore the months where the number sold were greater than the average = January, Feburary, May, July, August and September and highlight these values Red

[Jihwan_Kim]

Hi,

It is hard to tell without understanding how the data model looks like, but I think, in that case, the average measure has to remove filers that are applied except [Site] column.

[AJW2023]

Data is very basic:

[Jihwan_Kim]

Hi,

Thank you for sharing.

Do you mean something like below?