Group by one column and get average from another column

[Anonymous]

Hello all

I have following table

and I want to get with DAX avg for each character, so result should be this

unfornatelly GROUP BY is not working as I expect..can anyone help me to understand where I am wrong?

and what should be correct formula ?

[Anonymous]

Hi @Anonymous 

The GROUPBY() function returns a table, so you can get the right outcome in calculated table:

Or you can change the measure into this:

MEASURE =
VAR _AVGTable =
    GROUPBY (
        ALLSELECTED ( 'Table' ),
        'Table'[Char],
        "_AVG", AVERAGEX ( CURRENTGROUP (), 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _AVGTable, [Char] = SELECTEDVALUE ( 'Table'[Char] ) ), [_AVG] )

The result is as follow:

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

thanks, and 3 more mini questions 🙂 before I click on Accept as a solution
1) can I use summarize instead of group by ? is better?

2) why are used functions

    -ALLSELECTED in table ? is it neccessary ?
     -MAXX (when FILTER is iterator of the filtered table)

    -  [Char] = SELECTEDVALUE ( 'Table'[Char] ) ? Sorry I dont get it, because this condition will be always true

sorry to bother you, but I want to understand the logic of it 😞

[Anonymous · Accepted solution]

Hi @Anonymous 

Sure, the SUMMARIZE() function can instead of the GROUPBY():

MEASURE 2 =
VAR _vtable =
    SUMMARIZE (
        ALLSELECTED ( 'Table' ),
        'Table'[Char],
        "_AVG", AVERAGE ( 'Table'[Value] )
    )
RETURN
    MAXX ( FILTER ( _vtable, [Char] = SELECTEDVALUE ( 'Table'[Char] ) ), [_AVG] )

As my testing, measure2 runs faster than measure and seems to use less memory.

About ALLSELECTED() function, it's not necessary in table, in measure, the ALLSELECTED() function is often used.

This link can be help:

Understand the Filter Context and How to Control i... - Microsoft Fabric Community

The _AVGTable returns a table:

[Char] = SELECTEDVALUE ( 'Table'[Char] ) //The [Char] is the [Char] column in _AVGTable, the SELECTEDVALUE ( 'Table'[Char] ) returns the [Char] in Original Table, this function is to get the right outcome from the virtual table _AVGTable.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.