Conditional Format Matrix on Average of row total

[JamesGordon]

Hi all,

I am trying to add some conditional formatting to a matrix table but struggling to get the correct result.

I am trying to make the value in the table turn either Green or Red. Green if the value in the cell is greater than the Average of the row total and Red if it is below. Screenshot attached with the result i am after.

i.e if the total is £155,750 and there are 4 entries then £155,750/4 - £38,937.5 Any values greater than this in that row can be Green. Obviously each row has different averages.

I have created a measure to calculate the average per row-

myAverage = divide(sum(Sheet[Total Sell]),distinctcount(Sheet[Rep]))

But i cannot get the conditional formating to work?

Any ideas?

[Anonymous]

Hi @JamesGordon,

Can you please share a pbix or some dummy data that keep raw data structure with expected results? It should help us clarify your scenario and test to coding formula.

How to Get Your Question Answered Quickly  

Regards,

Xiaoxin Sheng

[Greg_Deckler · Accepted solution]

@JamesGordon Have you checked that calculation in context of the individual cells? Seems like you would need something like:

myAverage = divide( CALCULATE(sum(Sheet[Total Sell]),ALLEXCEPT(Sheeet[Make])),CALCULATE(COUNTROWS(DISTINCT(Sheet[Rep])),ALLEXCEPT(Sheet[Make])))

[JamesGordon]

@Greg_Deckler 

Thank you,  thats appears to give me the average of the row total so getting nearer. But how can i now make that figure colour coordinate the individial cells?

[AlexisOlson]

Try coloring based on a measure like

SUM ( Sheet[Total Sell] ) - [myAverage]