Color Coding Values in Power View Maps Based Upon Positive/Negative Sign

Power View can be a good tool for interactive data visualization and data discovery, but it has a few limitations with its mapping capabilities.

  • Power View only visualizes data on maps using bubbles/pies.
  • The size of the bubble on a Power View map can be misleading during analysis for data sets with small values and negative values.
  • By default, Power View can only display one measure on a map, which it uses to determine the size of the bubble.

Microsoft offers other mapping capabilities in Power Map and SSRS maps, so if all you need is a map, you can explore those other tools to determine if they better meet your needs. Check out my presentation on Choosing Your Geospatial Mapping Tool for more info on requirements, capabilities, and limitations of maps in Power View, Power Maps, and SSRS. If you want to see a map in the context of other charts, and you want the interactive highlighting and filtering of Power View, you may look for workarounds to the second and third issues listed above.

I recently had a client express disappointment about the display of negative values in Power View maps. A negative value simply gets a small bubble, and positive value gets a larger bubble. You have to hover over the bubbles to see each value and understand the bubble size that represents the change in sign from positive to negative. This gets a little tedious and minimizes the usefulness of the bubble map in the first place. The example Power View map below shows the year over year change in trespassing incidents in my city from 2013 to 2014. The small bubble has a value of -16, and the large bubble to the right has a value of 6.

PV Map Neg Values

One way to better present this type of data in a Power View map is to have positive values represented in one color and negative values represented in another with the bubble sizes showing the absolute value of the change. This proved to be less than straight forward as I worked my way through it. You can’t just make separate measures for the positive and negative values because Power View won’t let you put two measures on a map in that manner. You can’t simply create a calculated measure that determines the sign of your values and put it in the legend because Power View is expecting categorical data for the legend and it won’t let you put the numerical data field in the legend. Power View also doesn’t recognize calculated measures that return a text value (they don’t show up in the field list), so you can’t create a measure that turns the sign into a text value (“Positive” or “Negative”) in an effort to make it more categorical.

But you can use dynamic segmentation to show the second measure as a dimension. There is a great video on MSBI Academy that walks you through this. Although the video shows two different measures on a map, you can use this method to color code positive and negative values of a single measure. First, create a disconnected table for the number sign attributes. It should contain at least:

  • a text field with a descriptor
  • a field for the minimum value of the range
  • a field for the maximum value of the range.

The video shows storing that table in Excel (which is great if you plan to make changes), but you can now also paste the table directly into Power Pivot without any link to the table in Excel. My final table looks like the image below.

PV Map Sign Table

Next you need a calculation that uses the disconnected table and displays the absolute value of the measure you want to use for the bubble size.

Change In Incidents YOY Pos/Neg:=
     if(CALCULATE(Countrows('Sign'),
     FILTER('Sign',[Change in Incidents YOY] >= Sign[Min] 
     && [Change in Incidents YOY] < Sign[Max])
     ),
     abs([Change in Incidents YOY])
     )

Then you can build your map.  Use the Pos/Neg field for color, the new calculation for size, and your location fields of choice.

PV Map Pos Neg 1 Layer

Note that you will now see a warning message in the Power View fields list that relationships may be needed. You can disregard that. The use of a disconnected table is the cause of that message, and everything is working as intended.

PV Rel Msg

If your map has no drill down levels, you can stop here. Your map will now have the values for positive and negative in the legend with bubbles of two colors on the map. A bubble whose signed value is 5 will be the same size as a bubble whose signed value is -5 with the color indicating the sign of the number. In this analysis, a positive number indicates an increase in trespassing, so I chose a theme and ordered my values in the sign table such that the positive values were assigned the brighter color. These are the bubbles that need attention since trespassing is rising in the related zip codes.

If you do have drill down levels in your map, you have a couple more steps. You need another measure that deals with the drill down path. Without this extra step you end up with pies and unexpected values.

PV Map Pos Neg 2 Layer Bad

My map allows the user to drill down from zip code to beat. My new calculation references the top level of my drill down path ([Zip Code]), the calculation I just created ([Change In Incidents YOY Pos/Neg]), and the second level of the drill down path in my map ([Beat]). Note that this calculation is specific to the drill down path used in the map. If you have several maps with different drill down paths, you will need to create several calculations for this to work.

Change In Incidents YOY Pt 2:=
     IF(COUNTROWS(ALLSELECTED('KCPD Crime Data 2014'[Zip Code]))=1, 
     [Change In Incidents YOY Pos/Neg], 
     CALCULATE([Change In Incidents YOY Pos/Neg], 
     ALLSELECTED('KCPD Crime Data 2014'[Beat]))
     )

Now I can put this new measure in place of the original one, and the pies will go away, returning the expected color coded bubbles and values.

PV Map Pos Neg 2 Layers SBS

7 thoughts on “Color Coding Values in Power View Maps Based Upon Positive/Negative Sign

  1. Hi There,

    I am facing similar problem, except that I have to categorize my data in 3 groups(pos/neg in your case.
    I have measure eg, % in a table of Data Model(Change in Incidents YOY in table KCPD crime data 2014, in your case) that needs to be categoize in a range 1. <20%, 2. Between 20% and 40% and 3. More than 40%. I did some tweaks with the solution you shared, but it didn't worked for me.
    It would be really helpful, if you could provide the work around to have size of bubble based on % and color based on category.

    Thanks a ton in advance.

    Rahul

    1. HI, Rahul. Check out this DAX Pattern, which is very similar to what I’ve done in my blog post: http://www.daxpatterns.com/dynamic-segmentation/. Your disconnected table would have columns for the Segment name, min % change and max % change, and segment order. You will have 3 rows for your 3 segments. Since you have 3 levels of geography, your calculation would have 2 if statements to check if you have only 1 country and then one province selected (to avoid the pie chart situation described above). Let me know if you run into a specific issue.

      1. Hi Meagan,

        I think it hasn’t worked for me 😦

        Let me put this in more detail
        I have Churned_Data table in Data Model with columns Country, City, Province, TT, Ven, Month, Year, TOTAL_COUNT and CHURNED_COUNT. Each row in the table is unique. I have created Calculated field as Churned%:=CALCULATE(DIVIDE(SUM([TOTAL_COUNT]),SUM([TOTAL_LSN]),0))
        Another Calculated field as RANGE:=SWITCH(TRUE(),[Churned%]0.2 && [Churned%]0.4,3 ).

        Segment Table that I have created is
        Range_Group Description MinValue MaxValue
        1 Less than 20% 0 30
        2 Between 20% and 40% 31 40
        3 More than 40% 41 100

        What will be the DAX query to have color based on above segment?

        Kindly let me know if you need more clarity.

        Regards,
        Rahul

  2. Hello Meagan,

    Your solution done the trick for me 🙂 Thanks a lot.
    But I am facing problem will the Drill Down. There are still lots of Pie appearing on the Map with the DAX expression I have used. Below is the expression that is being used.
    CHURNED_DRILLED%:=IF(COUNTROWS(ALLSELECTED( Churned_Data[COUNTRY] ))=1,
    [Churned%] ,
    IF(COUNTROWS(ALLSELECTED( Churned_Data[PROVINCE] ))=1,
    [Churned%],
    CALCULATE([Churned%],
    ALLSELECTED(Churned_Data[CITY] ))
    ))
    Where, [Churned%] is calculated field with DAX expression as Churned%:=CALCULATE(DIVIDE(SUM([CHURNED_COUNT]),SUM([TOTAL_LSN]),0))

    Regards,
    Rahul

  3. Hi, Rahul. That’s a pretty specific issue. I would need to see your model to be able to try a few things. If you want to send it to me you can DM me a link on twitter. My handle is @mmarie.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s