Forum Discussion
Maintaining bubble scaling during filtering
I've created a map using the Azure map visualization whre each location is represented by a bubble, and the scale of the bubble is based on the count of a metric at that location. I've added a filter that allows a user to select year (2018, 2019 or 2020), and the map updates so that the bubbles represent count by location for that year.
I want the scale of the bubbles to remain the same across all years, so that change in count by location year-over-year is visable. Currently when the user selects a year, the bubble size scales proportional to count for that specific year. This results in a larger bubble in 2018 compared to 2019 where the 2018 location count is a larger proportion of the total 2018 count, but the 2019 count at the location is higher than the 2018 count at the location.
Does anyone know how to keep the bubbles scaled to same proportions when filtering between different years?
11 Replies
- parry2k
Super User
Anonymous well you have to revisit the measure you have created. Maybe share your sample pbix file using one drive/google drive or try this measure:
Count = COUNTROWS ( Table ) % of each Location Across All Years = DIVIDE ( [Count], CALCULATE ( [Count], ALL () ) )✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
- parry2k
Anonymous I guess instead of the count, you should calculate some sort of % across all the years for each location so that filtering on the year doesn't have an impact on the bubble size.
✨ Follow us on LinkedIn
Learn about conditional formatting at Microsoft Reactor
My latest blog post The Power of Using Calculation Groups with Inactive Relationships (Part 1) (perytus.com) I would ❤ Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos to whoever helped to solve your problem. It is a token of appreciation!
⚡ Visit us at https://perytus.com, your one-stop-shop for Power BI-related projects/training/consultancy.⚡
v-jingzhangCommunity Support
Hi Anonymous
Assume your original count measure is Count('table'[fieldname]), you can use the following measure to get the total count for all locations across all years. This will remain filters on other columns if you need that.
total = CALCULATE ( COUNT ( 'table'[fieldname] ), ALL ( 'table'[Location] ), ALL ( 'table'[Year] ) )Then use this measure to get the percentage of above total.
percentage = DIVIDE ( COUNT ( 'table'[fieldname] ), [total] )Or combine them into one
percentage = DIVIDE ( COUNT ( 'table'[fieldname] ), CALCULATE ( COUNT ( 'table'[fieldname] ), ALL ( 'table'[Location] ), ALL ( 'table'[Year] ) ) )Hope it helps.
Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.v-jingzhang solution you proposed is almost working once I swapped out count for sum. percentage formula is calulating % share by location though, need denominator to be sum across all locations.
parry2k v-jingzhang I'm not able to share the underlying pbix file but have posted screenshot to hopefully help solve. perhaps I'm missing something but neither solution posted has fixed the issue yet. Do these screesnhots help?
The problem still seems to be size of bubbles are relative to total proportion of negative hours at each circuit for the filtered year, not proportion for all years.
