Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago
Solved

GROUPBY + ALLEXCEPT functions

Hello, I have this table from which I want to get the average qty per order.

1) I have this measure to help me achieve it.

Measure =

Var Group_1=

GROUPBY(

'Table',

       'Table'[Month],

       'Table'[Order],

       'Table'[Product Code],

                             "Sum",sumx(CURRENTGROUP(),'Table'[Qté]))

Return

Calculate(Averagex(Group_1,[Sum]),ALLEXCEPT('Table','Table'[Month]))

 

2) The result is not what I want. I used the allexcept function (see in red above) to see the same result for every product code depending on the month. I should see 6 for every product in Jan and 7 for every product in Jun.

 

 

Please note that I gave a simple generic example, my real formula contains nested GroupBys so I need a solution that will still use groupby function.

 

Thank you!

 

 

Need Help
Tips and Tricks
  • AlexisOlson's avatar
    AlexisOlson
    4 years ago

    I think something simpler might work. Is this what you're after?

    Measure =
VAR Group_1 =
    GROUPBY (
        'Table',
        'Table'[Order],
        "Sum", SUMX ( CURRENTGROUP (), 'Table'[Qté] )
    )
RETURN
    AVERAGEX ( Group_1, [Sum] )

     

3 Replies

  • jeroendekk's avatar
    jeroendekk
    Responsive Resident
    4 years ago

    Hi Anonymous 
    It would help if you can post your sample data in a format that can be copied, so people here can make mock report to check if their proposed solution works.

    Best regards,
    Jeroen

  • AlexisOlson's avatar
    AlexisOlson
    Super User
    4 years ago

    I think something simpler might work. Is this what you're after?

    Measure =
VAR Group_1 =
    GROUPBY (
        'Table',
        'Table'[Order],
        "Sum", SUMX ( CURRENTGROUP (), 'Table'[Qté] )
    )
RETURN
    AVERAGEX ( Group_1, [Sum] )

     

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi Anonymous ,

     

    I tested the solution provided by AlexisOlson  and was able to get the correct result.

    If the problem has been solved you can mark the reply for the standard answer to help the other members find it more quickly. If not, please point it out.


    Best Regards,
    Henry


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