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

unique-user-nam — Tue, 29 Nov 2022 09:46:09 GMT

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!

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

Anonymous — Tue, 29 Nov 2022 10:02:15 GMT

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!

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

unique-user-nam — Tue, 29 Nov 2022 14:26:52 GMT

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

topic Re: Create dynamic measure comparing value in each row against context-based average in DAX Commands and Tips

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

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

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