Forum Discussion

JamesGordon's avatar
JamesGordon
Icon for Helper II rankHelper II
4 years ago
Solved

Conditional 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 i...
  • Greg_Deckler's avatar
    Greg_Deckler
    4 years ago

    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])))
Greg_Deckler's avatar
Greg_Deckler
Icon for Community Champion rankCommunity Champion
4 years ago

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's avatar
JamesGordon
Icon for Helper II rankHelper II
4 years ago

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's avatar
    AlexisOlson
    Icon for Super User rankSuper User
    4 years ago

    Try coloring based on a measure like

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