Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
4 years ago

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...
Need Help
v-jingzhang's avatar
v-jingzhang
Icon for Community Support rankCommunity Support
4 years ago

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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    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. 

    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      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.

    • v-jingzhang's avatar
      v-jingzhang
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi 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

      • Anonymous's avatar
        Anonymous
        Not applicable
        4 years ago

        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.