Sales Effectiveness (Quotes v Orders) with Filled Map

[MarcusHussey]

Good Afternoon

I am trying to measure Sales Effectiveness in a given US State (using Zip Codes) by comparing the number of Quotes to the number of Orders in the given Zip Codes; my Data Set is an Excel file broken out into 3 columns (Zip Code, Orders, Quotes).  When I pull the Data into a Filled Map the results are not exactly as I would like them. 

(I will try to "copy & paste" screen shots into this Text Box, but after 3 attempts I have not been able to.....)

In the first example, I have the Zip Codes in the Location field, and Quotes in the Legend field.  As you can (or cannot) see on the Legend for the below image, I have defined the Colors for the number of Quotes in the Top Left - but all of the Zip Codes are colored with the same color.  In addtion, if you hover over each Zip Code the Flag states each Zip Code has 0 quotes - regardless of what my Data Set says.

Image 1......

.....

In the second example, I have Zip Codes in the Location field, Orders in the Legend field, and Quotes in the Color Saturation field.  Here again I have color coded the Legend to represent the number of Orders in a given Zip Code - and this time it works.  One of the 2 problems with this example is the Color Saturation for the number of Quotes does not change.  If a Zip Code has 0 Quotes or if it has 20 Quotes - the Saturation level is the same. 

The 2nd issue I have with this example is that for certain Zip Codes the Filled Map is showing incorrect data.  When certain Zip Codes are next to each other, it will only show 1 Zip Code (with correct data called out in the Flag) but place it where the other Zip Code should be.

Image 2......

......

There is a plan to add another layer to this report (Opportunities in a given Zip Code), but I do not want to tackle that until I resolve the above.

Thank you

-Marcus

[MarcusHussey]

OK....I was able to figure out how to post images to this area (it looks like Power BI changed this area from yesterday??).

Here is Image 1 in the above example (all Zip Codes are the same color)

Here is Image 2 in the above example (can't control Color Saturation)

Thank you

-Marcus

[v-qiuyu-msft]

Hi @MarcusHussey,

I'm able to reproduce the issue in my environment, I will report it internally and get you update once I get any feedback.

Best Regards,
Qiuyun Yu

[MarcusHussey]

Thank you

[MarcusHussey]

So I received word back from Microsoft today regarding this issue......

The current most of the behavior is by design.

The filled map currently doesn’t fully support something in Location and Legend without something in Color Saturation; it's meant to be an intermediary state right now. In upcoming updates we will be trying to address this issue which should correctly determine which to use, assuming a 1-1 relationship between categories and legend items.

For the issue with the misplaced zip code, will open a ticket with Bing Maps team, but the best thing to do is to manually add the state to the zip codes by concatenation with a calculated column, such that the zip codes take the form of “79405, Texas.” We do not currently take drill hierarchy into account when geocoding, but it is an issue we are aware of.

The (blank) legend item is coming from what seems to be an empty row at the very bottom of the report. Because it ends up sorted as the first item for the legend, it’s currently used as the series for all zip codes when there is no value. It isn’t included when there’s a value because when we have values, we remove any rows that do not have values. You can see the same behavior in table. Please remove the extra blank row if you do not want to see it.

......

After trying their suggestions, I have come up with the following:

1.  I was able to add the “, Texas” designation to all of the Zip Codes and this returned accurate results in the Filled Map.
2. Transforming my data in Excel into a Table removed all of the “Blank” designations when 1 item is being queried in “Legend”…..
3. ….though not being able visually see the results from 1 item being queried is, in my opinion, a miss; that hopefully will be corrected in a subsequent revision to Power BI as you point out below.
4. When an item is added to the “Color Saturation” field, there are still errors to the Saturation levels that are being returned.