topic Re: Group by one column and get average from another column in DAX Commands and Tips

Group by one column and get average from another column

Anonymous — Mon, 24 Jun 2024 20:13:54 GMT

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 ?

Re: Group by one column and get average from another column

Anonymous — Tue, 25 Jun 2024 01:20:31 GMT

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.

Re: Group by one column and get average from another column

Anonymous — Wed, 26 Jun 2024 18:59:15 GMT

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 😞

Re: Group by one column and get average from another column

Anonymous — Thu, 27 Jun 2024 01:24:51 GMT

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.