Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
05-08-2021 03:36 AM
Summary
"How is my company doing?"
It's a question we hear often. It seems simple enough, but answering it is a complex process. This report is designed to answer the question in a clear and straightforward manner, while providing enough detail to take some action.
If we wanted to further develop this story, we would build on the outcomes of our Sales Scorecard by adding additional reports with more granular data. Each of these report tabs would answer one specific question that arises from use of our Sales Scorecard.
Approach
A report is most valuable when it's actionable. We started by identifying which components of the process we can influence:
We then determined the report's focus: areas that are not performing well. We used color to highlight these problem areas, and leveraged a matrix with conditional formatting to give us granular insights that could prompt strategy changes. Finally, we included comparisons of profit and sales against prior years to put the performance into perspective. We leveraged basic Power BI elements (cards, shapes, tree maps, and DAX) to develop a KPI section in the top right of these charts to provide additional context.
From this report it's clear that we should evaluate our training programs. We may want to pick up the phone and follow up with William, who seems to be struggling in several areas, most notably in OSHA supplies which is a profitable area for all other sales staff.
We might also want to look further into the strange profit behavior in Mar 2013, where we saw a large dip compared to the prior year.
A report like this is the first step into answering the question "How is my company doing?"
Pushing the Boundaries
Every so often you have a clear vision of how to communicate something, but you aren't quite able to realize that vision with the default tools or options. Custom visuals is one method to help realize your vision, but I'm not a D3 expert and I like a challenge, so sometimes I'll attempt to repurpose default tools in non-standard ways to achieve the same effect.
I had a vision for what I wanted my KPI visualization to look like:
The main elements are:
I started with a card for my main value, the % Growth Year over Year. I then created 2 dax measures to format my Current Year and Prior Year values nicely for display (using FORMAT to add "CY: " or "PY: " in front of the value for context). I put each of those into separate cards and arranged them next to my % Growth card.
Finally, I created the indicators. I made a triangle shape, colored it grey, and oriented it upwards. I made a second triangle shape, colored it red, and oriented it downwards, and aligned it directly under the upward triangle. I then created 2 dax measures called KPI Red and KPI Grey.
KPI Red = 1 when % Growth is Positive and 0 when % Growth is Negative.
KPI Grey = 0 when % Growth is Positive and 1 when % Growth is Negative.
I then put each of those into individual tree maps, changed the data colors to match the background, and arranged the tree maps over the tops of the triangles. Now when % Growth is negative, KPI Red is 0 and the tree map disappears and shows the red triangle underneath, while KPI Greyis 1 and the tree map covers the grey arrow.
Wrapping it up
The method above worked pretty well and got me what I wanted. However there are a few oddities - light white lines that outline the tree maps, the arrows are in different locations if they are up or down (because they're 2 different triangles), and the CY and PY are centered rather than left aligned since they're in cards.
Ultimately, this got us where we needed to go for now. However, as a next step I would begin investigating how to turn this into a custom visual to make future use much simpler. For now, it's great to know that we can leverage basic Power BI elements like triangles to create custom KPIs. It just takes a bit of thinking outside the box, plus some creative use of DAX and willingness to use standard visuals (tree maps) in non-standard ways (covers to hide something).
4/12 Update: Simplify using UNICHAR
I just discovered the UNICHAR function in DAX, and used this to simplify my process! Currently we have 2 elements for each indicator, for a total of 8 elements. This method cuts the elements in half!
With this method, we are using the UNICHAR to display UNICODE arrow characters. Since we're doing this in DAX, we can us IF statements to hide/show the arrows based on our measures, which means we no longer need to use the tree maps as covers, cutting our number of elements in half! Less elements = easier to maintain.
Enjoy!
Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
eyJrIjoiZjQ0NWYwYzEtYjE1OC00MWExLWEwYjgtMGMwZmFmZTZiYTUyIiwidCI6IjdlY2M4YWEwLTgzMGUtNGQ4Ni1hZjc4LTgzYTNkY2MyNjIzOCIsImMiOjZ9
Hello JaredK,
Would you mind sharing the Pbix file for this report with me? my email is Sam_YL_Shen@wiwynn.com. Thank you.
It's impressing to show out YoY performance with a Card. And I also like your correlated 2 line graphs between prior-year(in gray) and this-year(in black).
Best regrads,
Sam
Hello Jared,
I'm new to learning PowerBI. Would you be willing to share the pbix? canuckmtn.girl at outlook.com to assist in learning by example. Much appreciate your consideration.
Karen
Не плохой дашборд за счет цветового решения. Информация размещена не системно, но внимание привлекает.
Не плохой дашборд за счет цветового решения. Информация размещена не системно, но внимание привлекает.
Awesome visualization.
Can you help me with the .pbix file. Please mail me @ monicarathi9@gmail.com
Thanks in Advance 🙂
Hi @Anonymous ,
Appreciate if you can share the .pbix file via kimkhanh312@gmail.com
Thanks in advance.
Nice
Can you help me with the .pbix file, I really appreciated if you could send me via email chavez.aact@gmail.com
Thanks! 😄
Great Visualization! Can you help me with the .pbix file, I really appreciated if you could send me via email tingibson@gmail.com
Thanks!
This is outstanding DB. Would you kindly share the pbix & data files to pallavimandal.1911@gmail.com
Many thanks in advance!
@Anonymous wrote:
Summary
"How is my company doing?"
It's a question we hear often. It seems simple enough, but answering it is a complex process. This report is designed to answer the question in a clear and straightforward manner, while providing enough detail to take some action.
If we wanted to further develop this story, we would build on the outcomes of our Sales Scorecard by adding additional reports with more granular data. Each of these report tabs would answer one specific question that arises from use of our Sales Scorecard.
Approach
A report is most valuable when it's actionable. We started by identifying which components of the process we can influence:
- What we're selling (products)
- Where we're selling (region, state)
- When we're selling (month, year)
- Who is doing the selling (salesperson)
We then determined the report's focus: areas that are not performing well. We used color to highlight these problem areas, and leveraged a matrix with conditional formatting to give us granular insights that could prompt strategy changes. Finally, we included comparisons of profit and sales against prior years to put the performance into perspective. We leveraged basic Power BI elements (cards, shapes, tree maps, and DAX) to develop a KPI section in the top right of these charts to provide additional context.
From this report it's clear that we should evaluate our training programs. We may want to pick up the phone and follow up with William, who seems to be struggling in several areas, most notably in OSHA supplies which is a profitable area for all other sales staff.
We might also want to look further into the strange profit behavior in Mar 2013, where we saw a large dip compared to the prior year.
A report like this is the first step into answering the question "How is my company doing?"
Update 4/12/2017: You can use the DAX UNICHAR function to do this more simply. See the update section below.
Pushing the Boundaries
Every so often you have a clear vision of how to communicate something, but you aren't quite able to realize that vision with the default tools or options. Custom visuals is one method to help realize your vision, but I'm not a D3 expert and I like a challenge, so sometimes I'll attempt to repurpose default tools in non-standard ways to achieve the same effect.
I had a vision for what I wanted my KPI visualization to look like:
The main elements are:
- % Growth Year over Year (main value)
- Current Year value (adds context)
- Prior Year value (adds context)
- Indicator (quick recognition of good/bad state)
I started with a card for my main value, the % Growth Year over Year. I then created 2 dax measures to format my Current Year and Prior Year values nicely for display (using FORMAT to add "CY: " or "PY: " in front of the value for context). I put each of those into separate cards and arranged them next to my % Growth card.
Finally, I created the indicators. I made a triangle shape, colored it grey, and oriented it upwards. I made a second triangle shape, colored it red, and oriented it downwards, and aligned it directly under the upward triangle. I then created 2 dax measures called KPI Red and KPI Grey.
KPI Red = 1 when % Growth is Positive and 0 when % Growth is Negative.
KPI Grey = 0 when % Growth is Positive and 1 when % Growth is Negative.
I then put each of those into individual tree maps, changed the data colors to match the background, and arranged the tree maps over the tops of the triangles. Now when % Growth is negative, KPI Red is 0 and the tree map disappears and shows the red triangle underneath, while KPI Greyis 1 and the tree map covers the grey arrow.
Wrapping it up
The method above worked pretty well and got me what I wanted. However there are a few oddities - light white lines that outline the tree maps, the arrows are in different locations if they are up or down (because they're 2 different triangles), and the CY and PY are centered rather than left aligned since they're in cards.
Ultimately, this got us where we needed to go for now. However, as a next step I would begin investigating how to turn this into a custom visual to make future use much simpler. For now, it's great to know that we can leverage basic Power BI elements like triangles to create custom KPIs. It just takes a bit of thinking outside the box, plus some creative use of DAX and willingness to use standard visuals (tree maps) in non-standard ways (covers to hide something).
4/12 Update: Simplify using UNICHAR
I just discovered the UNICHAR function in DAX, and used this to simplify my process! Currently we have 2 elements for each indicator, for a total of 8 elements. This method cuts the elements in half!
- Delete the tree maps and the arrow objects
- Delete the KPI Red/Green measures for Sales and Profit (4 total)
- Create 4 new measures
- Sales Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]>0),UNICHAR(11205),"")
- Sales Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Sales YoY Growth]<0),UNICHAR(11206),"")
- Profit Indicator Negative = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]<0),UNICHAR(11205),"")
- Profit Indicator Positive = IF(AND(HASONEVALUE('Date'[Fiscal Year]),'Sales Scorecard'[Profit YoY Growth]>0),UNICHAR(11206),"")
- Put each measure in a separate card.
- Change the Data Label colors to make the down arrows red and the up arrows grey
- Arrange your arrows and you're done!
With this method, we are using the UNICHAR to display UNICODE arrow characters. Since we're doing this in DAX, we can us IF statements to hide/show the arrows based on our measures, which means we no longer need to use the tree maps as covers, cutting our number of elements in half! Less elements = easier to maintain.
Enjoy!
Jared Knutzen | Visualization Consultant
Decisive Data | www.decisivedata.net
eyJrIjoiZjQ0NWYwYzEtYjE1OC00MWExLWEwYjgtMGMwZmFmZTZiYTUyIiwidCI6IjdlY2M4YWEwLTgzMGUtNGQ4Ni1hZjc4LTgzYTNkY2MyNjIzOCIsImMiOjZ9
Hello Jared,
Would you mind sharing the Pbix file for this report with me? my email is gjorgez@gmail.com
Very impressive and I want to learn how to make it. can you share the pbix file with me? liga810@gmail.com
This is a very impressive dashboard and has been very helpful for me while making a functional dashboard for business insight. Thanks again for sharing your experience. One of the areas I am stuck with is YOY%. I would appreciate if you can share the pbix file at ajithkumar2k@gmail.com?
I really like what you've done with this. Asking and answering questions with few colours making it easier to focus on the information. The trend explanation blocks are very helpful. However, the Profit Trend arrows are the wrong way round, which is confusing. Increased profit has a red downwards arrow instead of a grey upwards arrow, easily fixed in your measures.
@Anonymous
Can you please elaborate creation of "What is Sales Trend?" visual!
It will be very helpful for me.
Thank you
Hola el cuadro de mando muy bueno! ¿Serías capaz de compartir el pbix o simplemente describir cómo hiciste lo rentable / no rentable como una leyenda? y como hago un grafico de tendencia
Hi,
would you be willing to share this Pbix file with me? My email is drmuralimca@gmail.com
@Anonymous
Hello Jared.
It's a wonderful dashboard. I really want to learn from it. Would you mind sharing the PBIX file with me to linhdanghoang@gmail.com?
@AnonymousThis is such an awesome dashboard! Do you mind walking me through how you created this dashboard?
Hello Jared,
Would you mind sharing the Pbix file for this report with me? my email is guidotreur@yahoo.com
Thank you