cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
AlanRGroskreutz
Helper I
Helper I

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.

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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/#

 

SU18_powerbi_badge

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

 

View solution in original post

2 REPLIES 2
AlanRGroskreutz
Helper I
Helper I

Thanks, that did the trick. 

AlB
Super User
Super User

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/#

 

SU18_powerbi_badge

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

 

Helpful resources

Announcements
Microsoft Build 768x460.png

Microsoft Build is May 24-26. Have you registered yet?

Come together to explore latest innovations in code and application development—and gain insights from experts from around the world.

charticulator_carousel_with_text (1).png

Charticulator Design Challenge

Put your data visualization and design skills to the test! This exciting challenge is happening now through May 31st!

May UG Leader Call Carousel 768x460.png

What difference can a User Group make for you?

At the monthly call, connect with other leaders and find out how community makes your experience even better.

Power BI Dev Camp Session 22 768x460.jpg

Check it out!

Mark your calendars and join us on Thursday, May 26 at 11a PDT for a great session with Ted Pattison!