Forum Discussion

anna-lee's avatar
anna-lee
Icon for Helper I rankHelper I
3 years ago

Help with Avg measure

I currently have a matrix that looks like the below.   I'm trying to write a measure to conditional format for the measure '01 TOT' that will compare it to the average of ALL the vend...
amitchandak's avatar
amitchandak
Icon for Super User rankSuper User
3 years ago

anna-lee , Try like

 

Measure = 
Var _tot = [01 TOT]+0
var _all = calculate([01 TOT], allselected(Table[Vendor]) // or calculate([01 TOT], removefilters(Table[Vendor])
SWITCH(
TRUE(),
_tot = 0,"white",
_tot < _all, "#red",
_tot > _all, "#green",
)

 

 

very similar what I have done here

Scatter Quadrant -Segment in 4 quadrants based on Margin % and Discount, Use Constant Line- https://youtu.be/0k_C_E7YOQY

  • anna-lee's avatar
    anna-lee
    Icon for Helper I rankHelper I
    3 years ago

    Hi amitchandak 

    Your formula's weren't calculating the average. I tried both:

    CALCULATE(AVERAGEX('Table',[01 TOT]),ALLSELECTED('Table'[Vendor Code]))
    and 
    CALCULATE(AVERAGEX('Table',[01 TOT]),ALLEXCEPT('Table','Table'[Year]))

    but they both are returning the same incorrect results.