GROUPBY + ALLEXCEPT functions

[Anonymous]

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!

[v-henryk-mstf]

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.

[AlexisOlson · Accepted solution]

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] )

[jeroendekk]

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