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

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

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):

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 !

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