Forum Discussion

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago
Solved

Mode mesaure for Virtual Table

Hi, I'm trying to create a MODE measure and apply to a given virtual table given below: VAR VrtTable= UNION( ROW("VrtValue",[Product Count]), ROW("VrtValue",[-1M]), ROW("VrtValue",[-2M]), ROW("Vrt...
  • OwenAuger's avatar
    OwenAuger
    4 years ago

    Sure, just saw your update 🙂

     

    I would then lump the two conditions (nonblank [VrtValue] and [Frequency] > 1 ) into a FILTER around GROUPBY:

    VAR VrtTable =
    UNION (
        ROW ( "VrtValue",[Product Count]),
        ROW ( "VrtValue", [-1M] ),
        ROW ( "VrtValue", [-2M] ),
        ROW ( "VrtValue", [-3M] ),
        ROW ( "VrtValue", [-4M] ),
        ROW ( "VrtValue", [-5M] )
    )
VAR ValueFrequency =
    FILTER (
        GROUPBY ( VrtTable, [VrtValue], "Frequency", SUMX ( CURRENTGROUP (), 1 ) ),
        NOT ISBLANK ( [VrtValue] ) && [Frequency] > 1
    )
VAR Mode =
    -- MINX returns the smallest value in case of ties
    MINX ( TOPN ( 1, ValueFrequency, [Frequency] ), [VrtValue] )
RETURN
    Mode

    Regards,

    Owen

George1973's avatar
George1973
Icon for Helper V rankHelper V
4 years ago

..And one more thing - The grouping should ignore BLANK values. As given below:

The mode of that highlighted group should give 2400 as the Mode result. Now it's blank.

 

OwenAuger's avatar
OwenAuger
Icon for Super User rankSuper User
4 years ago

Sure, just saw your update 🙂

 

I would then lump the two conditions (nonblank [VrtValue] and [Frequency] > 1 ) into a FILTER around GROUPBY:

VAR VrtTable =
    UNION (
        ROW ( "VrtValue",[Product Count]),
        ROW ( "VrtValue", [-1M] ),
        ROW ( "VrtValue", [-2M] ),
        ROW ( "VrtValue", [-3M] ),
        ROW ( "VrtValue", [-4M] ),
        ROW ( "VrtValue", [-5M] )
    )
VAR ValueFrequency =
    FILTER (
        GROUPBY ( VrtTable, [VrtValue], "Frequency", SUMX ( CURRENTGROUP (), 1 ) ),
        NOT ISBLANK ( [VrtValue] ) && [Frequency] > 1
    )
VAR Mode =
    -- MINX returns the smallest value in case of ties
    MINX ( TOPN ( 1, ValueFrequency, [Frequency] ), [VrtValue] )
RETURN
    Mode

Regards,

Owen

  • George1973's avatar
    George1973
    Icon for Helper V rankHelper V
    4 years ago

    This is it! You made it! :))))))
    Thank you very much!
    Works perferctly