Highlighting measure values above average for each row in a Pivot table

[pagnonig]

Hi all,

I built a table where rows are different [Areas], columns are [month-year] and measure is the sum of [Cost].

Now, for each row, I would like to highlight in red those values that are above the average of the monthly values of [Cost]. The average calculated as the sum of the monthly [Cost] values (as displayed in the pivot) per each row divided by the number of months.

I get the months from a Count (distinct  [month-year] ).

Then I use the formatting function:

if (Sum([Cost]) >= Aggr(Sum( [Cost]), Areas)/ Count (distinct  [month-year] ), red() )

Unfortunately it does not work. For some reason the check is done only for the first column of the pivot but not for the others.

If I just try to display Aggr(Sum( [Cost]), Areas) as a measure [Test] I see that only the first column (relevant to the first month) is populated while the others are not. I would expect to have the aggregated total replicated the same for each month...

(Please note that some months are missing on the right of the attached image so the values do not add up to the total in the screenshot)

Do you have any clue about what I'm missing? Please feel free to ask any other additional info may be useful for troubleshooting.

Thanks for your help!

Giuseppe  

[maheshkuttappa · Accepted solution]

Try this, I have count( distinct total transaction_date)  assuming that you need all months irrespective of whether Area has any data in that month.

if (Sum([Cost]) >= Aggr(nodistinct Sum([Cost]), Areas)/ Count (distinct total [month-year] ), red() )

[pagnonig]

Thanks, it works flawlessly!!!😀

May I ask and additional favor, how would you:

* highlight those values that are above the average + standard deviation of the monthly values for that area

* highlight the highest and the lowest monthly value

Well.. it's two favors... I hope it's doable 🙏

Giuseppe