Problem with Total average of Averagex on rows with weeks for a specific period.

[Anonymous]

Hello.

My problem is that i want to show the total average of an averagex function for all the rows, as i can to use it with color formatting (as a benchmark) -  but I cant seem to figure it out. 
I've tried using the following code:

Total average on rows =
 var _result = 

calculate ( averagex (Values( 'Dim - Date'[W+"WeekNo"]), [measure)] ) , 
FILTER('Dim - Date', [IsLast12Weeks] = "Yes" ), ALL('Dim - Date'[W+"WeekNo"] ) ) 

return 
_result

 My [measure] is a simple count. In the following picture you can see that the Total of the averagex code is the number i want, but on every week (row), so i can compare it to the measure, and color format it.

I hope somebody can help. 

[v-robertq-msft · Accepted solution]

Hi, @Anonymous 

According to your description, I guess you want to make the [Total average on rows] display the same amount for each row, which is the average for all weeks, and compare with the [measure], and compare the value with [Measure] then color format it, you can try this measure:

Total average on rows =

calculate ( average ( [measure] ) ,

FILTER(ALL('Dim - Date'), [IsLast12Weeks] = "Yes" ) )

To color format the [Measure], you can follow my step:

1. Create a measure:

Color =

IF([Total average on rows]>=[Measure],"GREEN","RED")

2. Go to the table chart setting->Conditional formatting->Background color On->Advanced control, and set like this:

And you can get the table like this:

You can download my test pbix file here

More info about Conditional formatting of table chart

If this DAX can not achieve your expected result, you can post some sample data(without sensitive data) and your expected result, so that we can help you in advance. Thanks very much!

How to Get Your Question Answered Quickly 

Best Regards,

Community Support Team _Robert Qin

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