Blog

The Pareto principle in Power BI

Product Managers love data. Therefore, being able to create your own reports to track the KPIs relevant for your products is really useful. One of the KPIs you might run into relates to the Pareto principle, for example, 80% of the revenue needs to come from 20% of the customer base.

The Pareto principle

I’m going to refer you to the wikipedia for the extensive explanation. I will give you a couple of examples:

  • 20% of the customers should account for 80% of the revenue.
  • 20% of the richest persons in earth account for 80% of the income.
  • 20% of Italy’s population owned 80% of its land.

The Pareto principle in Power BI

We take a couple of steps to get this visualised in Power BI:

  1. Prepare the data structure.
  2. Adding a rank to the data based on a specific category.

As an example I’m using the KPI that 80% of revenue should come from 20% of the customers.

Data preparations

I recommend you to create a table to put the data you want to visualise on the screen. This makes it easy to validate the results of the measures you create.

Adding a table to a Power BI report.
Adding a table to a Power BI report.

You need two types of data to visualise the Pareto principle in Power BI:

  1. Category: this can be, for example, a customer or country.
  2. A number: this can be, for example, the sales of a customer or country.

Ranking your customers

You first need to find the top 20% of your customers. You should create a measure that calculates the rank of the customer depending on the sales value.

Rank = RANKX(ALL(Sales[Customer]), CALCULATE(SUM(Sales[Sales_Value])))

When you add the Rank measure to your table and sort it by sales value, the customer with the highest amount of sales should have rank 1.

Cumulative sales

Now that we have ranked your customers, we want to calculate the cumulative sales. This measure cumulative sales of the customer with rank 1 is the sum of the sales of customer 1. The cumulative sales of customer 2 is the sum of the sales of customer 1 and customer 2.

(use SHIFT+ENTER to create a new line in DAX).

Cumulative_Sales_Value = 
var currentRank = [Rank]

RETURN SUMX(FILTER(ALL(Sales[Customer]),[Rank]<=currentRank), CALCULATE(SUM(Sales[Sales_Value])))

Total sales

To calculate the stake of the cumulative sales of a customer in the entire revenue you need to calculate the total sales value. The ALL function applies the selected filters.

Total_Sales = CALCULATE(SUM(Sales[Value]), ALL(Sales[Customer]))

The last measure you need to create calculates the cumulative sales (in % of total sales). You will use this measure to create the line in the visualisation of the Pareto principle.

Cumulative_Sales_Perc = [Cumulative_Sales_Value] / [Total_Sales] * 100

Create a line and clustered column chart

Now you are ready to add the measures into a line and clustered column chart.

Adding a line and clustered column chart to your PowerBI report.
Adding a line and clustered column chart to your PowerBI report.
Assigning the Customer, Sales_Value, and Cumulative_Sales_Value_Perc to the line and clustered column chart.
Assigning the Customer, Sales_Value, and Cumulative_Sales_Value_Perc to the line and clustered column chart.

The result:

An example of the result of visualising the Pareto principle in Power BI.
An example of the result of visualising the Pareto principle in Power BI.

Automatically push .NET libraries on NuGet

Getting the source code of my home automation project on Github was a first step. Now, I’m ready to post some components on NuGet as well. This makes it easier to manage the packages and reduces the dependencies between my Visual Studio projects.

I ran into a challenge though: how could I easily upload my projects to NuGet without having to use the command line? Luckily, Visual Studio has something called Post-build event command line.

Post-build event command line; you can find this in the properties / build events tab of your project.

The command put into this block is executed after finishing the build. The only thing you need to do manually is change the package version, otherwise NuGet will rightfully decline the push (the version already exists).

You need to manually update the package version for NuGet to accept the push.

Depending on your directory and project structure you should be able to get the command below up and running. You can easily test this in the Command Prompt, just make sure you browse to the right target directory.

cd bin\Debug && dotnet nuget push *.nupkg -k xxx -s https://api.nuget.org/v3/index.json --skip-duplicate

Of course, you should replace the xxx with the NuGet API key.

Reduce disk space consumption in Windows 10

Windows 10 can be quite hungry when it comes to disk space, especially when the system has been used for several years and has been updated several times. In some situations temporary update files are “stuck” in the C:\Windows\WinSXS directory, which at that time can consume up to 17Gb of disk space. Microsoft has published an article describing the process to clean up this directory in more detail. However, this doesn’t always work as described.

I recommend you to do the following:

  1. Download the latest version of System Cleanup, an old but ad-free program that scans the system for temporary files.
  2. Run the Disk Clean-up (as administrator!) and clean-up the system.
  3. Use Dism, the command line deployment tool, the merge existing update images into the current installation using the commands below. This might take several hours to complete. You might need to repeat them once or twice to take effect.
Dism.exe /online /Cleanup-Image /StartComponentCleanup /ResetBase
Dism /Online /Cleanup-Image /RestoreHealth
Running System Cleanup, an old but ad-free tool that scans the system for temporary files.
Starting Disk Clean-up as administrator. Make sure you tick all necessary boxes for clean up.

These steps helps helped me to free up roughly 12Gb of storage space on a small little tables that only had a 32Gb drive. Its users can now enjoy using it again for the occasional e-mail and card game, without worrying about a lack of disk space and, as a result, missing important security updates.

Publishing an existing .NET (Core) project to Github

I started working on my home automation project in 2020. I started with a simple backup script that copies the source code to a second hard drive, just in case the primary hard drive fails. When my code base started to grow I extended the script to create a (daily) second copy on OneDrive. From a backup perspective this is working fine, but it does not give me versioning and source control. Luckily, Github exists. Microsoft developed a Github extension for Visual Studio.

Make sure you update your Visual Studio installation before you install the extension. You also need to install Git for Windows. Additionally you need a Github account. After that, publishing an existing project to Github is pretty straightforward.

Open the command line and browse to the directory of the project. Execute a git init.

D:\>cd DataMario\Ontwikkeling\IoT\Verhaeg.IoT.Geofencing.Sensor

D:\DataMario\Ontwikkeling\IoT\Verhaeg.IoT.Geofencing.Sensor>git init
Initialized empty Git repository in D:/DataMario/Ontwikkeling/IoT/Verhaeg.IoT.Geofencing.Sensor/.git/

D:\DataMario\Ontwikkeling\IoT\Verhaeg.IoT.Geofencing.Sensor>

Open Visual Studio and copy/paste an existing gitignore file to exclude configuration files that could include passwords.

Then push the project to Git:

Push the project to Git service…

And finally, define the Git repository details.

Visual studio will offer you to create a Github repository.

Once you have published the project on Github you need to maintain it. There are three steps required to upload changes to the Github repository:

  1. Pull: pulls the latest version of the repository to the local development machine.
  2. Commit: commits the code changes to a “snapshot” of your project.
  3. Push: pushes the latest snapshot of your project to the repository.