12-20-2016 12:52 PM - last edited 05-30-2017 09:20 AM
Thank you so much for your comment @fenixen, appreciate the encouragement! Would love to see what you come up with!
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:
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
Regarding the Profitable/Unprofitable coloring in the bar charts, I am using color saturation. I have two measures, Sales and Profit.
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.
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
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!
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.
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?
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!
@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
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?
Thank you very much.
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.