Create dynamic measure comparing value in each row against context-based average

[unique-user-nam]

Hi there, new to the forum and DAX here.

I'm trying to create, for each store in a row, a measure named [Ranks] that compares 'cleaned'[TO Efficiency] and 'cleaned'[OCR] with the respective context-based averages, i.e. if I apply filters, the measure values change based on the filtered data as well. 

Ranks = SWITCH(
    TRUE(),
    cleaned[TO Efficiency] < cleaned[Avg_TO_Eff] && cleaned[OCR] > cleaned[Avg_OCR], "1",
    cleaned[TO Efficiency] < cleaned[Avg_TO_Eff] && cleaned[OCR] < cleaned[Avg_OCR], "2",
    cleaned[TO Efficiency] > cleaned[Avg_TO_Eff] && cleaned[OCR] > cleaned[Avg_OCR], "3",
    cleaned[TO Efficiency] > cleaned[Avg_TO_Eff] && cleaned[OCR] < cleaned[Avg_OCR], "4"
)

The above code, although produces one single value which is not what I want (my understanding is it's calculating for the entire table instead of for each row), illustrates what I want to achieve.

Eventually I want to assign 4 diffrent colors that change according to filter context to the bubbles scattered in four different quadrants delineated by the two averages.  

Any tips would be greatly appreciated. Thanks!

[Anonymous]

Hi @unique-user-nam ,

I think you should use a new column instead of a new measure.

Best Regards,
Shreya Mukkawar

Appreciate with a Kudos!! (Click the Thumbs Up Button)
Did I answer your question? Mark my post as a solution!

[unique-user-nam]

Thanks for the reply! But aren't values in a column static? Will they recalculate if I apply filtered?