Measure for max by group

[itchyeyeballs]

Hi All,

I'm looking to turn this calculated column into a measure:

XtPoints_Scaled = 
VAR Xi = Core_Data_All[XTPOINTS]
VAR MnX = CALCULATE(MIN(Core_Data_All[XTPOINTS]),FILTER(Core_Data_All,Core_Data_All[Year]=EARLIER(Core_Data_All[Year])))
VAR MxX = CALCULATE(MAX(Core_Data_All[XTPOINTS]),FILTER(Core_Data_All,Core_Data_All[Year]=EARLIER(Core_Data_All[Year])))
RETURN DIVIDE(Xi-MnX , MxX - MnX)

I'm struggling to find a way to dynamically calculate the max and min values for a group using the current row context, for example, min xtpoints where year = 2017.

The above syntax only works as a calculated column which means my filters don't get applied etc.

Is there a way to achieve this? It seems simple!

[Icey]

Hi @itchyeyeballs ,

Is this problem sloved?

If it is sloved, could you kindly accept it as a solution to close this case?  

If not, please let me know.  

Best Regards

Icey

[itchyeyeballs]

Hi

Thank you for the response and apologies for not responding earlier.

It didn't fix the issue as I needed to retain other filters being applied to get the correct subset of data, I guess I could have expanded your solution but I ended up doing the calculations during the data prep process, made the model very slow to update but gets there in the end.

Thank you

[Icey · Accepted solution]

Hi @itchyeyeballs ,

Maybe you could try this:

Measure =
DIVIDE (
    MAX ( Core_Data_All[XTPOINTS] )
        - CALCULATE (
            MIN ( Core_Data_All[XTPOINTS] ),
            ALLEXCEPT ( Core_Data_All, Core_Data_All[Year] )
        ),
    CALCULATE (
        MAX ( Core_Data_All[XTPOINTS] ),
        ALLEXCEPT ( Core_Data_All, Core_Data_All[Year] )
    )
        - CALCULATE (
            MIN ( Core_Data_All[XTPOINTS] ),
            ALLEXCEPT ( Core_Data_All, Core_Data_All[Year] )
        )
)

Best Regards,
Icey

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.