Month: January 2015

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

2014 in Review

2014 was a wonderful, challenging, exhausting, exciting year. I grew a lot as a person and as a BI professional. This blog grew in content and in popularity.  I’d like to take a moment (and several paragraphs) to celebrate the great opportunities and great people who made my year special.

Speaking engagements

I gained more experience as a speaker/presenter in 2014 and marked a number of firsts in this part of my career. In addition, I got to travel and see many lovely places and meet good people.

Along the way, I met several people who deserve some recognition for their efforts as event organizers, coordinators, and volunteers:

  • I met Paras Doshi (t|b) at SQL Saturday #305 in Dallas, and he gave me the opportunity and encouragement to speak at the PASS BA Virtual Chapter. He gave a great presentation at SQL Saturday about How To Train Your Business Users To Create Their Own BI reports.
  • Miguel Escobar (t|b) and Diana Betancourt(t) put on a successful inaugural SQL Saturday Panama. They were so friendly and welcoming. Miguel got on a Skype call before the event to make sure I had all the information I needed, and he provided recommendations of fun things to do while visiting. Diana Betancourt is congenial, and her enthusiasm is infectious. She was a great representative for Panama tourism (as a native with great travel tips), SQL Saturday (as a speaker and volunteer), and the PASS community (as a friendly face) at the event.
  • Marc Beacom (t|b) organized the first Mile High Tech Con in Denver. He (and his team) went out of their way to make sure all of the speakers felt appreciated and comfortable throughout the conference. I hope I get to speak at MHTC next year! I also had some great conversations with him that I truly appreciate about BI and career topics.
  • Lance Harra (t) helped us organize SQL Saturday Kansas City this year. He brought lots of great experience and ideas and helped us be better organized and less stressed. We couldn’t have done it (or at least wouldn’t have wanted to do it) without him! He also supported his fellow Kansas Citians by attending my presentation at PASS Summit. It was nice to have a friendly face in the room.
  • SQL Saturday #332 (Minnesota) was the best organized SQL Saturday I’ve attended to date. They had plenty of volunteers and great support from sponsors. Congrats to Rick Krueger(t|b), Mike Donnelly (t) and the rest of the team.

I also had the opportunity to be in the audience to experience some impressive and inspiring presentations from other speakers:

Blog

I made a goal to blog at least once a month in 2014. Although I didn’t get a blog post in exactly every month, I did end up with 12 blog posts by the end of the year. Other interesting statistics from my blog include:

  • My blog is less than 2 years old and has been viewed over 14,000 times.
  • The blog post with the most views in 2014 was Retrieving Lowest Level Hierarchy Members and Leaves in MDX.
  • The most common search term (of those available) that led people to my blog was “ssrs 2014 visualization”.
  • The most popular links from my blog to other resources go to Stephen Few’s Perceptual Edge.

New Job

In December, I joined BlueGranite, a business analytics consulting firm with an excellent reputation and many interesting projects and opportunities for a BI developer like me to enhance my skills. I’m still settling in, but I have to say that it is a pleasure to work with some of the nicest and most talented people I’ve ever met. My new career opportunity is due in large part to the PASS community. Many SQL People helped me make new connections, gave me great advice and encouragement, acted as a sounding board, listened to me vent my fears and frustrations during the job hunt and transition, and then celebrated my new opportunity with me. A few that stand out are David Klee, Bill Fellows, Tim Ford, John Morehouse, Marc Beacom, and Hope Foley. My former job and speaking experience prepared me well for this new opportunity, but networking in the SQL Community made it happen. I went to PASS Summit and told my friends and colleagues that I was interested in new opportunities. They gave me great leads and good contacts, and less than 3 weeks later I had a new job.

2015 Goals

I’m taking Q1 off from speaking so I can rest up and settle into my new job. I probably won’t speak quite as much as I did last year, but I will definitely submit to speak at some SQL Saturdays and other conferences.

I plan to publish 18 blog posts this year. While most posts will be technical in nature, I’d like to write a couple posts about my experience as a virtual employee who works from home.

I’ve become a mentor to a college student who thinks he might be interested in BI and process improvement. I’d also like to get more involved with my local PASS chapter. And I’d still like to try to make a SQL BI Cruise happen.

I’m looking forward to new friends, colleagues, opportunities and adventures in 2015.