Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Group by one column and get average from another column

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 ?

DAX
Group By
  • Anonymous's avatar
    Anonymous
    2 years ago

    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.

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      Anonymous
      Not applicable
      2 years ago

      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's avatar
        Anonymous
        Not applicable
        2 years ago

        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.