Most of our customers use different types of classifications of their customers, products or services based on some rules or business logic set by the managerial teams. It is not uncommon that these rules are regularly revised and we are often tasked to supply the change or to update the models to analyze the new rules impact on, e.g., revenue. A lazy engineer as myself tends to ask the question: Could the task of providing new rules to the model be done dynamically and by the user instead of the BI professional?

The answer to that question is of course: YES!

The possibility to do this in Power BI has been around for some time now but due to the introduction of the DAX function GENERATESERIES it has become a lot easier and less strenuous. GENERATESERIES takes three argument: StartValue, EndValue and IncrementValue and returns sequential values from start to end with a step size defined by the increment value. To create a series between 1 and 10 with a step size of 1 we can write the following formula (with corresponding result):

The possibility to create these tables allows us to use values and/or ranges from them to change the rules of classification by user input. In this demonstration I will use a dataset containing 12027 randomly generated customers that we divide into 5 classes or segments based on the number of products they’ve bought. The company in this demo also wants to promote its customers with discounts based on which class they belong to. Given discounts should be set dynamically by the user.

A step by step guide on how to prepare tables and measures

Step 1: Create a table containing the number of wanted classes

This is easily done by using the Create Table-command with the following code:

Step 2:  Create one table for each class with the desired numeric ranges

The class ranges are created using the Create Table-command with the following code:

Step 3: Create “what-if”-parameters for the discount given to each segment

With the introduction of “What-if”-parameters to Power BI it is now easy to add parameters to your Power BI model using the user interface provided by Power BI. This procedure is repeated five times with the following input (names need to be changed every iteration):

The final data model contains a lot of tables, but do not fear! You can easily hide the tables from the end user and spare them of the table overload. The final data model looks like this:

Step 4: Build measures that effectively classifies the customers and sums up their sales including and excluding discounts taking user input into account

So, here comes the tricky part! There are probably several ways of achieving the wanted result but I have chosen a methodology that I find easy to understand and straight forward.

In order to decide the class belonging of each customer we need to know the boundaries for the classification, i.e. an upper and lower boundary for each class. This is achieved by using the DAX function SWITCH in combination with FIRSTNONBLANK and LASTNONBLANK accordingly:

The discount given to each customer class is calculated in an equivalent manner using the DAX function SELECTEDVALUE:

Now that we know the ranges for each class it is an easy task to classify and count the number of customers per class using the following formula:

This DAX expression creates a table containing the “Classes”-table and then, using row iteration, calculates the lower and upper boundary for each class. The number of customers in each class is calculated by counting the number of rows in the customer table that fulfills the criteria stated by the lower and upper boundaries. Last, but not least, the SUMX-function simply sums up the number of customers.

In a similar manner, the discounts and sales are calculated accordingly:

Demonstration in Power BI

To demonstrate how easy it now is to play around with the class ranges and the discounts, I’ve created and published the following report. When you change the parameter values you will notice how the results in the table and the waterfall-visual instantly changes. All the class ranges and discount tables that where created in the second and third step is added to the report using the numeric range slicer.

When working with this set-up it is important to notice that there should be no overlap between the class ranges. If there is any overlap, the result will have no real implication or value.

Try it out!

The problem I’ve been exploring in this post is generic and can be applied to a lot of different cases where you want to understand the implication of certain changes in your business model, systems or when you simply want to know “What happens if…?”.

The next time I will demo a nice take on how to analyze events with a duration in Power BI, such as “How many active orders where there 2017-05-31”, that was provided to me by Alberto Ferrari at SQLBI.com.

Until next time,

Victor