Create a nice-looking PBI Scoring System in 10 min (and make your Boss impressed)

by augustindelaf Member on ‎02-06-2018 07:26 AM - last edited on ‎02-08-2018 01:17 PM by MindyJ Frequent Visitor

The purpose of this tutorial is to create a nice visual that compares your products each to another, according to rules that you created.  In the end, your visual looks like a class of students where YOU give good/bad marks.

20be95_c186be1858a54501bac3e0a33e8fd4d9-mv2.png

 

I had the opportunity to present this dashboard (similar data model but different topic) at Microsoft France HQ in June 2017:

https://community.powerbi.com/t5/Data-Stories-Gallery/Business-Schools-Dashboard-France/m-p/187496.

 

You can find the Excel data set here: http://bit.ly/2BJpq0e.  You can also find it attached to this blog post as well as the PBI file.

  

Let's imagine we have a portfolio of products, and assigned values (Sales and profits figures) to these products. Our goal is to give a score to each product. Lowest Score is 'BAD', after that comes 'MEDIUM', then 'OK', and the highest score is 'GOOD'.

 

Our products are fruits: apples, bananas, grapes, etc. Sales Quantity values, as well as Profit values, will help us to give the appropriate score to each product. We also collected from Google pictures the link to every image that corresponds to our products.

 

Excel Data model:

 

We are now in Power BI, we imported the Excel file, and we can see the data in the Query Editor. So, let's get serious. 

How can we make that scoring visual?

 

Simple Thing! First, you have to check that 'Sales' and 'Profits' fields are tagged as 'Integers'.

Then, in the Query Editor, select the Tab 'Add a Column' and then select 'Conditional Column'.

You can now create your own rules, and easily define and manage thresholds.

 

In the above rule, every product that sold more than 750 units will get a 'GOOD' score. If we want to retarget that, we can just amend that rule.  Let's see if it functions. Yes it does!

 

 

Now we can do the same rules for the profits (with different thresholds, of course, create your own rules):

 

Great! We now have 2 Scores. We could add more scores, but this will be enough for the demo. The next step is to Unpivot the 2 '*Scoring' columns, to be able to manipulate scoring values as one single field.  After Unpivoting these columns, rename the new ones 'Scoring type' for the Attribute and 'Scoring value' for the Value.

 

 

We can now click 'Close and apply', and create a visual that will show the fruits according to their sales performance.  To achieve that, we must use a custom visual called Chiclet Slicer (find it in the Office Store).

 

1. Take that visual and put 'Product' and 'img' in the visual fields like on the example below.

 

2. Done? Ok.  So now, let's duplicate that visual 4 times.

3. For each visual, create a distinct DAX measure with the Quick Measure editor, by clicking right on the field 'Values' ('Premier Product' in our example). You will Select the measure 'Filtered Value' and fill the gaps like that: 'Product' as the Default value, "Scoring Value' as the Filter, and then select one of the 4 possible values.   Each measure will have a different Scoring value and a different name but except that they will be the same. 

 

 

 

 

4. You Have 4 'Chiclet Slicer' visuals and 4 measures, filtered from 'GOOD' to 'BAD'. Put each measure in the 'Values' field of Chiclet Slicers visuals (instead of 'Premier Product' that we had). Then, position each visual next to each other.  The visual with the measure 'GOOD' must be on top left and visual with the measure 'BAD' must be on the top right.

5. Give some formatting properties to show that Left values are good, right values are bad.  The background of the top-left Chiclet Slicer must be green, and background of top-right Chiclet Slicer must be red.

6. Create a Simple slicer and drag the 'Scoring types' field into it. Put it in the center, on top of Chiclet Slicers like that.

You now have a nice tool to compare your portfolio of products. Good job!

 

Attachment
Comments
by SteveYoungca Frequent Visitor
on ‎02-11-2018 07:06 PM

Nice Post.  Thanks for the share!!!

by augustindelaf Member
‎02-12-2018 04:24 AM - edited ‎02-13-2018 09:42 AM

@SteveYoungca hello ! Glad you like it Smiley Wink

 

Just a question, have you followed the tutorial step by step ?

If yes How long did it last, and did you find anything hard to do?

 

Many thanks !

Augustin de la Fouchardiere