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.
Hej
I am struggling with a sumx on the max value per category, really hope if someone could help me here!
I have a fact table as below which I would like to sum the quantity for each max FCround on the category , meaning sum all the values = 20
I have tried different measures but still I could not achive it, can anyone help here
thanks in advance
Jack
Hi @Captain_Jack909 ,
Here are the steps you can follow:
1. Create measure.
Measure =
MAXX(
FILTER(ALL('Table'),
'Table'[Category]=MAX('Table'[Category])&&
'Table'[FC round]=
MAXX(
FILTER(ALL('Table'),
'Table'[Category]=MAX('Table'[Category])),[FC round])),[Quantity])
Measure2 =
SUMX(
FILTER(ALL('Table'),
'Table'[Category]=MAX('Table'[Category])&&
'Table'[FC round]=
MAXX(
FILTER(ALL('Table'),
'Table'[Category]=MAX('Table'[Category])),[FC round])),[Quantity])
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hello @v-yangliu-msft
THANKS so much for spending time and trying to help me here ! really appreciate !
I have tried your suggestions as below in measure 1 and measure 2 , however its still not what I would like to achieve, I want to show and sum value per subcategory based on the max FC round from category,
I can now achieve that on row level , but there are some problems on the aggregated results, which does not sum up to 60
thanks a lot for your help !
Jack
@Captain_Jack909 This looks like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149
The pattern is:
MinScoreMeasure = MINX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
MaxScoreMeasure = MAXX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
AvgScoreMeasure = AVERAGEX ( SUMMARIZE ( Table, Table[Group] , "Measure",[YourMeasure] ), [Measure])
etc.
Hej Greg.
I have read the article, but maybe I am a little bit slow to follow, I have tried different things , but no sucess.
can you help to be more specific how to fix the caculation in my scenario .
thanks a lot !
Jack
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 |
---|---|
107 | |
100 | |
80 | |
63 | |
58 |
User | Count |
---|---|
148 | |
111 | |
94 | |
84 | |
67 |