Forum Discussion
Helper IIConditional Format Matrix on Average of row total
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-
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])))
4 Replies
- Greg_Deckler
Community Champion
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])))
JamesGordonThank 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
Super User
Try coloring based on a measure like
SUM ( Sheet[Total Sell] ) - [myAverage]
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
