top of page

Unleashing the Power of PowerBI: Dynamic KPI Trigger Analysis


Hello, still on why Microsoft powerbi is not just another visualization tool.

Another type of analysis you can do with PowerBI is what I call KPI Trigger.

What does this mean? Say you have a customer list and you want to know which customers meet a particular KPI e.g. customers with sales over $5000, a profit margin of at least 20% and at least 3 different transactions.

How do you dynamically show only customers that meet this KPI without having to manually flip through?

Let’s go into PowerBI to see this. We would be using a very simple and straightforward data model for this


Data Model


Also, in order to achieve our objective, I created 3 other supporting tables as seen below


Sales KPI


Profit Margin KPI


Transactions KPI


Note that each of these supporting tables has it’s own DAX measures i.e.




We would be dynamically selecting our KPI which is the reason for the essence of the supporting tables and their associated DAX measures.

Let’s create a simple set of charts to show the sales, profit margin, and no. of transactions for each customer.



As you can see from the image above, just for Q1–2016 alone, we have a long list of customers who bought products from the organization.

As the data analyst in the organization, your manager comes to you with a request. Give me a list of customers who spent at least $3000 in the last quarter, their profit margin is a minimum of 25%, and also carried out over 3 transactions.

Do you want to start flipping through each row to identify which customers meet the specified criteria? If you have over a thousand customers, you will probably finish in 2–3 days. Efficient? No.

So what is the most efficient way? Think DAX, that’s where the magic lies

A simple DAX measure like this will solve the problem in seconds



What the DAX measure above does is, it only calculates, the total sales for only the customers that meet the specified condition.

Have a look



From the image above, 3 conditions were set

  1. Sales value of $3000 or more

  2. At least 3 transactions

  3. Profit Margin of 10% or more

We want to see only customers that meet those conditions. The table on the left shows the total list of customers who bought products in Q3–2014. Quite a long list evidenced by the scrollable arrow on the right.

While the bar chart shows only customers that meet the conditions specified. Quite easy right and very straightforward.

From the image above, we can see that Becky Martin had high sales and transactions but the profit Margin is low and hence doesn’t meet the criteria and was subsequently eliminated on the bar chart.

You can play around with the report here

I hope you have started seeing PowerBI as more than just another visualization tool.

With DAX, you can perform magic and be more efficient in the workplace

Comments


bottom of page