Forum Discussion

AlanRGroskreutz's avatar
AlanRGroskreutz
Helper II
5 years ago
Solved

Calculating difference from filtered mean/average

I am stuck trying to create either a calculated column or a measure that will give me an array of values calculated from two columns in a table.  The first column contains the groups in which the val...
  • AlB's avatar
    AlB
    5 years ago

    Hi AlanRGroskreutz 

    Try this for your calculated column

     

    New column =
VAR mode_ =
    MINX (
        TOPN (
            1,
            CALCULATETABLE (
                ADDCOLUMNS (
                    DISTINCT ( Table1[Value] ),
                    "Frequency", CALCULATE ( COUNT ( Table1[Value] ) )
                ),
                ALLEXCEPT ( Table1, Table1[Group] )
            ),
            [Frequency], 0
        ),
        Table1[Value]
    )
RETURN
    Table1[Value] - mode_

     

     

     

    and check this out for the MODE pattern

    https://www.daxpatterns.com/statistical-patterns/#

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

    Cheers

     

AlB's avatar
AlB
Community Champion
5 years ago

Hi AlanRGroskreutz 

Try this for your calculated column

 

New column =
VAR mode_ =
    MINX (
        TOPN (
            1,
            CALCULATETABLE (
                ADDCOLUMNS (
                    DISTINCT ( Table1[Value] ),
                    "Frequency", CALCULATE ( COUNT ( Table1[Value] ) )
                ),
                ALLEXCEPT ( Table1, Table1[Group] )
            ),
            [Frequency], 0
        ),
        Table1[Value]
    )
RETURN
    Table1[Value] - mode_

 

 

 

and check this out for the MODE pattern

https://www.daxpatterns.com/statistical-patterns/#

 

 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers