Sales Scorecard: Where are we losing money? by Decisive Data

Member
41630 Views
Member
Posts: 68
Registered: ‎12-20-2016

Re: Sales Scorecard: Where are we losing money?

Thank you so much for your comment @fenixen, appreciate the encouragement! Smiley Happy Would love to see what you come up with!

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
Member
Posts: 68
Registered: ‎12-20-2016

Re: Sales Scorecard: Where are we losing money?

Hi @mshparber, thank you! Correct, I tend to use reports more than dashboards. I find reports more useful as they are interactive, customizable ways to interact with the data. Dashboards feel very static to me, and I am not able to customize the presentation very much.

 

In general, I use:

  • Reports to layout the information and present a story
  • Dashboards to quickly navigate to reports

 

I think dashboards can be great to create collections of reports and make it easy to find them. However instead of pinning charts and visuals to a dashboard, I tend to create text boxes with report names and link them to my reports. Then I go to my dashboard and it's just a single click to my favorite reports Smiley Happy

 

Regarding the Profitable/Unprofitable coloring in the bar charts, I am using color saturation. I have two measures, Sales and Profit.

  • Sales = SUM('Sales Scorecard'[Sales Column])
  • Profit = SUM('Sales Scorecard'[Profit Column])

 

To create each bar chart, I am using Sales as the Value, and then choosing a different value for Axis on each chart. To create the coloring by profitability, I put Profit on Color Saturation.

 

SalesbyCategory.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

By default, this gives me a color gradient based on the value of Profit. As a best practice, I try to avoid gradients. For the purpose of this report, I want to call attention to areas that are unprofitable. If something is profitable I'm going to ignore it, because this dashboard is all about finding problem areas and fixing them - so I really want to make those problem areas pop!

 

I decided to use red to call out the problem areas, and gray for other areas (so I don't pay much attention to them). To do this, I chose a diverging color palette (because I only want 2 colors) and set Minimum to red and Maximum to gray.

 

Now, here's trick: I set the Minimum and Maximum settings as close to each other as possible, which removes the gradient and lets me choose exactly where the color changes. I want everything with a positive Profit value to be gray, so I set Maximum to 0. Now anything above 0 will show the Maximum color, which I've set to gray. Then I set the Minimum to -0.00001. Now, essentially the color can only be Minimum or Maximum and never anything in between! With this method, I just get 2 colors, and I never have a gradient because there are no values in between the Minimum and Maximum Smiley Happy

 

SalesbyCategorySaturation.PNG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Caution: You do have to be careful using this method if your values are very small or you could still get a gradient. One way around this is to create another measure, something like "Profitable" and set it to 1 or 0 depending on if it is profitable or not. Then you can use this secondary measure (more like a flag) on color saturation, and you know it will only ever be a 1 or a 0!

 

Hope that helps!

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
Established Member
Posts: 255
Registered: ‎07-28-2015

Re: Sales Scorecard: Where are we losing money?

@JaredK

Color saturation!

Minimum close to Maximum!

Just two colors!

Simply genius !

Thank you very much!

Michael

Occasional Visitor
Posts: 1
Registered: ‎01-12-2017

Re: Sales Scorecard: Where are we losing money?

Many thanks for sharing your approach with details to achieve simular. This is a great example of how to display alot of insignt into an area of the business in very little real estate space of the screen.

I am sure I will be refering to your post as a reference of what can be achieved with Power BI with new clients.

Great work.

Frequent Visitor
Posts: 4
Registered: ‎09-30-2016

Re: Sales Scorecard: Where are we losing money?

Hi @JaredK, thank you so much!!!! That tip is too helpful, I will reshare this on my PBI Dev team.

Regular Visitor
Posts: 22
Registered: ‎04-22-2016

Re: Sales Scorecard: Where are we losing money?

[ Edited ]

@JaredK

How are you doing comparison (e.g,. sales trend line graph) of current year vs prior year?  When I've done similar in a single visual, I've had to create two data sources referencing the same data but with different WHERE clause on each for the data range.  Is there some other way of using a single data source I'm missing?

 

Member
Posts: 68
Registered: ‎12-20-2016

Re: Sales Scorecard: Where are we losing money?

Hey @dennisr, sounds like you're trying to do this in SQL when bringing your data into Power BI. I'm using Time Intelligence functions in DAX to achieve this - they're super powerful!

 

I have 2 tables in my model: Sales Scorecard contains all the sales data, and Date is just a calendar table. I'm pulling 3 years of sales data into Power BI (2012-2014).

 

I created a base profit measure, based off my profit column in the data.

Sales = SUM('Sales Scorecard'[Sales Column])

 

Then, I used CALCULATE along with the SAMEPERIODLASTYEAR() function in DAX to create a prior year measure.

Sales (Prior Year) = CALCULATE([Sales],SAMEPERIODLASTYEAR('Date'[Fiscal Date]))

 

Finally, I wrapped this in an IF(HASEONEVALUE('Date'[Fiscal Year]) statement, because I only want this display if a single year is selected. It doesn't really make sense to show a prior year value if multiple years are selected.

Sales (Prior Year) = IF(HASONEVALUE('Date'[Fiscal Year]),CALCULATE([Sales],SAMEPERIODLASTYEAR('Date'[Fiscal Date])))

 

 

Hope that helps!

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
Regular Visitor
Posts: 22
Registered: ‎04-22-2016

Re: Sales Scorecard: Where are we losing money?

@JaredK  I found that using the "Legend" property of the line chart also allowed me to do year-over-year with a single source of data.  I'll look into the time functions.  Thanks

 

Visitor
Posts: 1
Registered: ‎03-22-2017

Re: Sales Scorecard: Where are we losing money?

Hi JaredK, You did a good job. This dashboard shows very quickly where we need to be focused.

 

What component did you use to paint "where are we unprofitable" matrix or table  and how did you paint it?

 

4-7-2017 12-23-30 PM.jpg

Thank you very much.

 

Adolfo 

Member
Posts: 68
Registered: ‎12-20-2016

Re: Sales Scorecard: Where are we losing money?

[ Edited ]

Hey @Adolfo, great question! I'm using the Conditional Formatting feature of Power BI. I chose to use a table because conditional formatting wasn't available in matrices when I first created this. You could do this in a matrix now, but I chose to stick with a table because I preferred the look and format.

 

Once you create your table/matrix, in the Values field well click the drop down next to the measure you want to use to color your cells. I wanted only 2 colors, so I set my min and max to be 0 - anything positive is grey, anything negative is red.

 

 

ConditionalFormatting.PNGConditionalSettings.PNG

 

 

 

 

 

 

 

 

 

 

 

Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net