Here's a short video also which highlights the insights that you are able to acheive with Power BI
First, let's set the scenario....we are working with sales data for an organisation that sells a variety of products throughout Europe.
Whenever I look to build out a model in Power BI I always like to note down specific questions that I want to answer. It gives me a much clearer direction for how I would like to build out my models.
In this case, I want to answer specific questions on my customers? So have a think about what they could be....
I thought of things like;
How are my sales going this year versus last year or the year before that?
How have my profits differed for each particular product through time?
What is the average margin achieved for customers and how have margins through time compared?
When was the last time we sold to a customer?
What were the most recent sales per region?
These are some solid questions, that nearly all businesses would like to answer if they are selling something.
That great news is we can answer these very effectively and efficiently in Power BI!
First I want to show you a picture of the data model, as this is important. You need to have a visual of how this is organised in the backend, to then understand how we can achieve the results we want in the front end.
The first thing you will notice is that we have some lookup tables. These include a table of dates, customer, products and regions. These all have a one-to-many relationship with the fact table, which is the sales table.
Structuring your models this way makes writing DAX code and creating reports far more intuitive.
You'll see we can already set up our filters from our lookup tables. It's a simple drag and drop from those relevant tables.
Now that we have our data model set up correctly we are ready to write some DAX that will get us the calculations we need to answer our questions.
I like to build out my measures where ever possible. Always start simple and then overlay some DAX coding patterns to get the more specific results.
Let's start with our core measures. Things like 'total sales', 'total profits', 'profit margins' etc.
See these example below
Now we've got our core measures done we can already do some great stuff. We have all our dimensions nicely set up in the data model and we have these measures that we can bring into a visual and then dynamically view these in any context we want.
From here we want to get more specific. We want to start answering these questions that are actually going to improve our understanding of our customers and consequently our decision making. This is where Power BI comes into its own!
We need to work out a few key things here.
Firstly, what were our sales last year and the year before that. Secondly, we have to analyse our information cumulatively. Then thirdly, we need to work out what the lifetime margins are for our clients.
All of these can be discovered with some patterns of DAX code.
First, let's start by writing measures to work out the sales in previous periods. This has been made relatively straight forward with the inbuilt DAX time intelligence functions. We are going to use here the SAMEPERIODLASTYEAR function within a CALCULATE statement.
Pretty easy right! The time intelligence functions do all the hard work for you. Placing this inside the CALCULATE statement is the key, as you want to view this result in the same context as your total sales results. This is only possible if you use the CALCULATE statement.
Now, what if we also quickly want to compare to the year before last? There's actually a few ways to do this but I'm going to show you the easy way I did it. See below
That's not too difficult right? All we had to do was place the SAMEPERIODLASTYEAR within itself and the time intelligence function was smart enough to work back 2 years instead of one.
So we've already discovered some new great information. We can already start filling in some of our report. All you have to do here is drag these new measures onto the report canvas and view them against the product and date contexts.
Hopefully, you can see how we are building out our measures now.
Now we move onto cumulative totals. This is a great DAX pattern. You will use this over and over again. Once you understand this pattern and use it once, you can re-use it anywhere, just sub in a new core measure.
Let's first start with finding the cumulative total for this year's sales.
Just to re-iterate this is a DAX pattern. You can re-use this. To describe what is happening here, you'll see that the FILTER and ALLSELECTED statements are removing any filters that are on dates, but only for the context which is selected in our date slicers in this case.
Then the function iterates through the date table and checks to see if each date is less than the current max date in the current evaluation context.
There's a bit to that. But read that a few times, and use this pattern multiple times and you'll quickly get the hang of it.
So now we have our cumulative total sorted, now we want to get our cumulative total also for last years sales and the year before that.
You know how just above I said we can re-use the cumulative total DAX pattern....well that exactly what we are going to do here. We are going to sub in our measures we calculated earlier into this pattern. See how I did this below.
All we did here was sub out the 'total sales' measure and then add in our 'total sales LY' and 'sales 2 yrs prior' measures. It's so great being able to re-use these patterns. It's really just a quick copy and paste when you get the hang of it.
Now we have all the calculations we need for a cumulative totals comparison chart.
Lastly we want to work out the lifetime margins on our customers, then compare each sale versus that margin.
We are able to do this by using the below DAX.
In the first instance, by combining CALCULATE with ALLEXCEPT we are able to see the total lifetime profit margins for each individual client. In the ALLEXCEPT function, we have said to ignore any context except for whichever is coming from the customers table. That means that if there is a filter from the date, products, or regions table ignore it. Only keep filters from the Customer Name column, which stems from our customers slicer.
Now all we have to do is overlay our profit margins measure from earlier and we are able to compare these two like the below.
We have covered a lot in this post!
There is actually even more contained in this dashboard, but hopefully you can see how you can draw such great insight from your data by utilising DAX calculations. It's just so much more efficient than in any other tools if you can get your head around a few key concepts of how DAX works.
I am also running a webinar that will run through step by step how to build this report. We will discuss in more depth the concepts that we have used here and when I developed it. I will also send out the demo data so that all participants can follow along as we build out the model and data visualisations. The webinar to totally free so looking forward to catching you then. To save your spot register here - http://enterprisedna.co.nz/webinars/