Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago
Solved

Find the mode average AND filter by a column

Hello,

 

Please can you tell me how to adjust the below code (from https://www.daxpatterns.com/statistical-patterns) for finding the mode average to also filter by a given column? (e.g. how do I find the mode average Data[Value] for a given Data[ProductType]

 

Thanks for your help,

 

CM

 

Mode :=
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ) )
        ),
        [Frequency],
        0
    ),
    Data[Value]
)

 

Need Help
  • v-ljerr-msft's avatar
    v-ljerr-msft
    8 years ago

    Hi Anonymous,

    Please can you tell me how to adjust the below code (from https://www.daxpatterns.com/statistical-patterns) for finding the mode average to also filter by a given column? (e.g. how do I find the mode average Data[Value] for a given Data[ProductType]

    Based on my test, the formula below should work. :smileyhappy:

    Mode =
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ), FILTER ( Data, Data[ProductType] = "A" ) )
        ),
        [Frequency], 0
    ),
    Data[Value]
)

     

    Regards

1 Reply

  • v-ljerr-msft's avatar
    v-ljerr-msft
    Icon for Microsoft Employee rankMicrosoft Employee
    8 years ago

    Hi Anonymous,

    Please can you tell me how to adjust the below code (from https://www.daxpatterns.com/statistical-patterns) for finding the mode average to also filter by a given column? (e.g. how do I find the mode average Data[Value] for a given Data[ProductType]

    Based on my test, the formula below should work. :smileyhappy:

    Mode =
MINX (
    TOPN (
        1,
        ADDCOLUMNS (
            VALUES ( Data[Value] ),
            "Frequency", CALCULATE ( COUNT ( Data[Value] ), FILTER ( Data, Data[ProductType] = "A" ) )
        ),
        [Frequency], 0
    ),
    Data[Value]
)

     

    Regards