Winner/Topper on Map: How to Color States on a Map with Winners/Runner-ups, Top Brand, Top Category

[amitchandak]

In Today's article, we would like to use an interesting solution. The solution is to color the map based on winners or runner-ups. We are going to use the sales data for this use case. And our winners are Brand, Category, Sub Category, City, Item etc.

For this, we have created sales data. We have used some random functions to generate this data. So, you might see some consistency in Data.

We have the following data points

1. Sales Fact. We have only base measures from the source excel. The rest of the measures/columns are created in Power BI. You can find calculation in the pbix file.
2. Geography Data
3. Item Data
4. Customer Data
5. Date - Calendar created in Power BI using the calendar function

From the base data, we have created the following measures

1. Gross Sales
2. Net Sales
3. Margin
4. Margin %
5. Discount %
6. Last month, Last Qtr, Last Year MOM, QOQ and  YOY for Net sales

But here we are only going to use the Discount % measure, and on Discount %, we have created Category and Brand Rank. So, we can filter Top rank on the Map.

Formulas:

Discount % = DIVIDE(SUM(Sales[Discount]),sum(Sales[Gross Sales])) 
Rank Discount % = RANKX(all('Item'[Category]),[Discount %],,DESC,Dense) 
Rank Discount % (Brand) = RANKX(all('Item'[Brand]),[Discount %],,DESC,Dense) 

This is what the Data looks like:

Relationship Diagram

We have used Shape Map, Map, and Filled Map Visuals.

And this is how the Topper looks like. Each graph is filtered on Rank =1 and the Legend is Category or Brand:

And this how the Runner-up looks like. Each graph is filtered on Rank =w and Legend is Category or Brand:

The Pbix file is attached to this article.

Please share your feedback and suggestions. Also looking forward to some interesting use cases that you want us to solve.

sales_analytics_v1.pbix