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.
I have the below data.
Fiscal Year | Fiscal Qtr | qtd | Total QTD | Max QTD |
2020 | Q1 | 10 | 100 | 40 |
2020 | Q2 | 20 | 100 | 40 |
2020 | Q3 | 30 | 100 | 40 |
2020 | Q4 | 40 | 100 | 40 |
2021 | Q1 | 5 | 80 | 35 |
2021 | Q2 | 15 | 80 | 35 |
2021 | Q3 | 25 | 80 | 35 |
2021 | Q4 | 35 | 80 | 35 |
I am trying to achieve the last two columns in the above table with two measures.
The qtd field is the raw data. I got the [Total QTD] to populate correctly with the measure.
[Total QTD] = CALCULATE(SUM('Table'[qtd]),ALL('Date'),VALUES('Date'[Fiscal Year]))
This shows the sum of qtd value for the year, for all quarters.
Similarly, I am trying to get the Max of qtd, by replacing the sum with 'max' in the same formula but it doesn't work.
Can someone please tell me how to get this?
Thank you very much in advance.
Solved! Go to Solution.
@Anonymous 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.
@Anonymous , you may want to try measures,
Total QTD = CALCULATE ( SUM ( Sales[qtd] ), ALLEXCEPT ( Sales, Sales[Fiscal Year] ) )
Max QTD =
MAXX (
CALCULATETABLE ( Sales, ALLEXCEPT ( Sales, Sales[Fiscal Year] ) ),
Sales[qtd]
)
Thanks to the great efforts by MS engineers to simplify syntax of DAX! Most beginners are SUCCESSFULLY MISLED to think that they could easily master DAX; but it turns out that the intricacy of the most frequently used RANKX() is still way beyond their comprehension! |
DAX is simple, but NOT EASY! |
@CNENFRNL Thank you for your response.
I tried your Max QTD measure but it's not returning the right values for me.
I need the Max QTD to get poulated with 2020 Q4 value = 40 for all 2020.
Please notice that the 40 is a sum() several different values, for example 20+10+10. The measure is populating Max QTD with 20. Beacuse 20 Is the max value for that quarter.
But I want it to take the sum of 2020 Q4 which is 40. and show it in the Max QTD.
Could you please help me acieve this?
Thanks in advance.
@Anonymous 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.
@Greg_Deckler I tried it with your second (A slightly more complex solution), making some small changes to it, and it worked.
Thank you very much 🙂
@Greg_Deckler I tried the solution from your post but it's still not working.
I need the Max QTD to show the MAX value of 'QTD' field which is a SUM(qtd) with Fiscal Year and Quarter filters.
Thank you for your response.
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 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |