Maintaining bubble scaling during filtering

[sdbpi]

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?

[v-jingzhang]

Hi @sdbpi 

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.

[sdbpi]

@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. 

[v-jingzhang]

Hi @sdbpi 

My previous code is for a measure not a calculated column. If you put it in a table visual, you will see result like below.

If you want to have a calculated column in the table, you can try below code. 

I attached a sample pbix below. Let me know if you have any questions. 

 

Regards,

Jing

[sdbpi]

ah my fault, thank you for that clarification. Using the 'percentage' code to create a measure, the percentage value at each location is specific to that location only. In other words, percentage is the share of hours at that location only, instead of share across all locations. For example location X has 0 hours in 2018 and 2019, but 100 hours in 2020. When I filter for 2020, the percentage value for the bubble at location X is 100% (2020(100))/(2018(0)+2019(0)+2020(100)), which is not representative of the total share (location X 2020(100))/sum of hours at all locations for all years.

[v-jingzhang]

Hi @sdbpi 

Is your data similar to my sample file that all data comes from the same table? What's your code for total section (denominator) in DIVIDE currently? Can you show the result of the total?

[sdbpi]

hi @v-jingzhang yes my data is in same format, all comes from single table. My measure code is below, I substituted my column names to match names from your date file. The code/math makes sense, just doesn't seem to work as intended when applied to sizing for bubbles on the map visualizations.

hours percentage =

SUMX('Table', 'Table'[Hours])/CALCULATE( SUM( 'Table'[Hours]), ALL('Table'[Year],'Table'[Location]))

[sdbpi]

sorry correction, the location field is not used on the map, the bubbles are based on latitiude and longitude assigned to location, lat and long are each from a seperate table. So this is my code now: 

hours percentage =

SUMX('table 1','table1'[hours])/CALCULATE( SUMX( 'table 1','table1'[hours]), ALL('table 1'[year]),ALL('table 2'[latitude]),ALL('table 3'[longitude]))

[sdbpi]

@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.

[parry2k]

@sdbpi 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.⚡

[sdbpi]

I'm still running into the same issue because the bubble sizes match the percentage at each location as a share of the total percentage across all locations for the year, instead of the percentage at each location as a share of the total across all three years

[parry2k]

@sdbpi 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.⚡