Mode mesaure for Virtual Table

[George1973]

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("VrtValue",[-3M]),
ROW("VrtValue",[-4M]),
ROW("VrtValue",[-5M])
)

Why I'm not able to work with real table? - Because the data of [Product Count], [-1m], [-2M], etc.. arguments are measure storing Sales in Current month and before.. (one m before, two months before etc..) So, I can not refer to the existing data table..

Unfortunately, "VALUES" and "COUNT" which are commonly used for mode measure can nor be applied to virtual table.

So, I would be very gratful if you could give me any idea.

Thanks in advance,

[OwenAuger]

Hi @George1973 

We can use GROUPBY to handle grouping of the virtual table:

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

Does this work as expected?

Regards,

Owen

[George1973]

Dear Owen,
The solution you've provided works almost perfectly. Just one criteria is needed:
Somehow we need to generate the MODE result only if FREQUENCY is MORE than ONE.

As you can see - The mode result is right when the frequency is more than one. So, Where should I put thia criteria, please advise.

Thanks in advance,

[OwenAuger]

That's great 🙂

In order to exclude cases where max Frequency = 1,

There are a few ways you could handle this. Here's one that applies a condition near the end to exclude cases where the max Frequency = 1./

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

Regards,

Owen

[George1973]

..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 · Accepted solution]

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]

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