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 values fall (Group), and the second contains the values (Value).  What I want to do is to calculate the mode of the values per group and then, per row, subtract the value from that mode.


Here is an example of how the calculations would look (the column Mode/Group isn't necessary and can be a variable)
'Data table'

GroupValueMode/groupCalculated Value
1743
2220
3660
1440
2422
1440
3660
2220

 

I want to be able to later show the maximum, minimum, and average value of this difference per group, and also to be able to filter it for other columns in this table. Therefore I'm not sure if a calculated column or a measure would be better. 

Thanks for any help.

  • 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

     

2 Replies

  • 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