Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to 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] )
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.
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] )
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
105 | |
93 | |
75 | |
62 | |
50 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |