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 one DAX question. Here is an example:
Country Product Price
Fra | P2 | 15 |
Fra | P3 | 19 |
Ger | P1 | 16 |
Ger | P3 | 20 |
Ita | P1 | 10 |
Ita | P2 | 11 |
Ita | P3 | 12 |
Ita | P4 | 10 |
I want to calculate average price of products per country (that's easy :)) and total average, but for total average each country should have the same weight factor. IE. I want average of average price per country for products.
I accomplished that as well. Here it is:
AvgPrice = AVERAGE(Table1[Price])
AvgOfAvg = AVERAGEX(VALUES(Table1[Country]) ; [AvgPrice])
My question is: is it possbile to obtain the same result with CALCULATE as with AVERAGEX? I simply cannot get the context right on a grand total level.
Also, I can achieve the same with addiotional query with GROUP BY or SUMMARIZE. But I wanted to create solution within a single measure.
So, this is more like DAX quiz, since I basically solved my initial task (or problem). 🙂
Thanks!
Solved! Go to Solution.
Hi @zvm,
Please try this measure:
average of average = VAR temptable = SUMMARIZE ( Test2, Test2[Country], "A", AVERAGE ( Test2[Price] ) ) RETURN SUMX ( temptable, [A] ) / COUNTX ( temptable, [Country] )
Best regards,
Yuliana Gu
Hi @zvm,
Please try this measure:
average of average = VAR temptable = SUMMARIZE ( Test2, Test2[Country], "A", AVERAGE ( Test2[Price] ) ) RETURN SUMX ( temptable, [A] ) / COUNTX ( temptable, [Country] )
Best regards,
Yuliana Gu
Thanks Yuliana
Not exactly what I asked for (calculate), but interesting hint for the other way to solve this otr for some other scenario,
You can nest row context inside iterations like AVERAGEX. Try this:
Avg Price = AVERAGE('Avg of Avg'[Price]) Avg of Average = AVERAGEX( VALUES('Avg of Avg'[Country]), AVERAGEX( VALUES('Avg of Avg'[Product]),[Avg Price] ) )
Thanks Nick.
I know a solution with averagex. I asked if there is a solution with Calculate to achieve the same result. 🙂
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 |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |