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.
Hi,
I am trying to create a measure which will allow me to show an average, based on the selection in a slicer. However, the slicer only implicitly selects the group of data, for example -
In a table of Country, City, TotalSales, I want to show have a measure of the average TotalSales for both a selected city and the country which that city is in (to allow for easy comparison). However, the country is not explicitly selected anywhere, and as such BI seems to be unable to calculate the average...?
E.g. by selecting 'London', one card will show the average total for London, whilst the other card now shows the average for England.
Please note that these are in separate cards for presentation reasons, not a single table!
I seem to get somewhere with GROUPBY() and SUMMARIZE(), but my experience of DAX is too limited to fully understand how to do this.
Any help would be much appreciated, thank you in advance!
Solved! Go to Solution.
Hi @JDLee23,
Thanks for @TomMartens's suggestion, it works fine in my test. But, it is not able to directly define a column name for variable, so, I made some modification:
Avg Sales Country = var currentCountry = LASTNONBLANK('table'[Country],1) return CALCULATE( AVERAGE('table'[TotSales]) ,ALL('table'[City]) ,'table'[Country] = currentCountry )
Best regards,
Yuliana Gu
Hi @JDLee23,
Thanks for @TomMartens's suggestion, it works fine in my test. But, it is not able to directly define a column name for variable, so, I made some modification:
Avg Sales Country = var currentCountry = LASTNONBLANK('table'[Country],1) return CALCULATE( AVERAGE('table'[TotSales]) ,ALL('table'[City]) ,'table'[Country] = currentCountry )
Best regards,
Yuliana Gu
Hey @v-yulgu-msft,
silly me, thanks for the adjustment.
Regards
Hey,
If there is a slicer that selects the city, these measures should work
Avg Sales City = CALCULATE( AVERAGE('table'[TotSales]) )
and
Avg Sales Country = var currentCountry = 'table'[Country] return CALCULATE( AVERAGE('table'[TotSales]) ,ALL('table'[City]) ,'table'[Country] = currentCountry )
Hope this helps
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |