Forum Discussion
Maintaining bubble scaling during filtering
Community SupportHi Anonymous
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
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 Anonymous
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?
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]))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]))