Create a Multi-Scoring Visual System in Power BI and make your Boss impressed ! (15min, Advanced Level)

11 janvier 2018

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.

Find the Excel data set here


Let's imagine we have a portfolio of products, and assigned values 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.

Data model is like that (Excel):

We are in Power BI, we imported the Excel file and we can see the 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 is 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 the visual fields (like on the example).


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 (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 (background of the top-left Chicet Slicer must be green, and background of top-right Chiclet Slicer must be red.)

6. Create a Simple slicer and drag into it the 'Scoring types' field. 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 !

Feel free to take a look at one of our beginner tutorials, by clicking here!

#ETL #PowerQuery #PowerBI #Scoring #Visual #Portfolio #PerformanceManagement #DAX #Unpivot #ChicletSlicer #CustomVisual #consultantPowerBIParis

Articles en relation

linkedin facebook pinterest youtube rss twitter instagram facebook-blank rss-blank linkedin-blank pinterest youtube twitter instagram