Power BI YTD – Quick Measures

Power BI YTD – Quick Measures

Hey guys, I’m back again….this time with tips and tricks that will turn the average Joe / Jane into Power BI superstars!! Yes, you heard right, Power BI has now made it so easy to do some of the Power BI formulas, that they’ve basically done all the work for you – awesomeness for sure!!

As you know by now, I really like stories – it’s just such a colorful way to “paint” a story to get your “picture” across (pun intended). Let’s take this guy, his name is Jake (Say hi Jake!!). Such a handsome fella isn’t he?!

mr library dude lego

Jake works for a very successful office furniture company (Lego Furniture & Accessories, I know, very original…), who sells all sorts of lovely office stuff. Jake is the finance manager and has lately really been struggling to get a grip on their company sales figures. He would like to be able to create a total sales by month report, year-to-date report to show how company sales have either increased or decreased over the past years, and what their total running sales has been for the past three years. While the business has been in operation for approximately 10 years already, Jake has almost always relied on manual number crunching using his trusty Microsoft Excel 2003 (We have all been there, haven’t we?). In the meantime, things have gone from bad to worst, and putting together the sales reports has become a painful and time consuming process. So what is our friend Jake to do? Jake happened to one day stumble across the Data Bear website, and well, one thing lead to another and he soon came to see the awesome thing, which is Power BI. He also discovered that he does not need to be a Power BI expert to create accurate and beautiful, easily update-able reports, month by month – thanks to quick measures. Lets see how Jake finally managed to get his reports sorted and gain the insights he so desperately needed.

Jake had the individual sales reports together in a folder, so he easily imported and combined all the necessary data into Power BI. For this story we won’t focus on how he did that.

Below we can see the data he has imported into Power BI – go Jake go!!

jake data

To summarize, he created the following measures:

  • Total Sales (Monthly) This is the base calculation for all the other measures relating to sales
  • Total Sales (Year-to-date)
  • Total Sales (Running Total)

It’s probably good to note that Jake also used our Dynamic Calendar Table tips to create an awesome and dare I say very dynamic Calendar, which we will use in our charts – so be sure to check it out!

While there are a number of “steps” in the process, it literally takes seconds to create these measures – So let’s create our three measures, shall we?!

Total Sales (Monthly) sales total measure 3

quick measures list

Step 1: Our basis for this calculation is a field called ‘Sales’. Right click on the Sales column in the ‘Fields’ Pane.

Step 2: From the pop-up box, select ‘Quick Measures’.

Step 3: Another pop-up box will appear with your quick measure variables. As you can see in the drop-down list to the right, you are able to choose from a whole variety of built-in quick measures. For our exercise however, we will only focus on the ‘Total for Category (Filters Applied)’, ‘Year-to-date total’ and finally the ‘Rolling Average’ quick measure. We can only create one at a time, so first, let’s choose the ‘Total for Category (Filters Applied)’ which will do what we need for the Total Sales measure.

Step 4: By default your base value will already be sales as that is what we selected in step 1.

Step 5: For our category, we will just add ‘Date’ from the Calendar table on the right, and drag & drop it into the ‘Category’ item on the left. Once done, click on OK.

Step 6: You will see now in the Formula pane, Power BI would have magically created the DAX formula for us – awesomeness!! But as our formula includes a filter called ‘ALLSELECTED’ – we can remove this by selecting everything after the SUM and deleting it. ‘ALLSELECTED’ is a useful function, but will cause some calculation issues for us in this example.

Step 7: In the Formula pane change the measure name to ‘Total Sales’ – press enter and you’re done.

The measure will now appear in the Fields list as shown below.

measures

If you followed this process you would have added your first Power BI quick measure – well done!!

For the other two formulas we will repeat this process but will use our newly created measure (Total Sales) as the basis for our calculations – let’s do this now.

Total Sales (Year-to-date)

jake ytd

Step 1: Our basis for this calculation is our previous created measure called ‘Total Sales’. Right click on the ‘Total Sales’ measure in the ‘Fields’ Pane.

Step 2: From the pop-up box, select ‘Quick Measures’.

Step 3: Another pop-up box will appear with your quick measure variables. Select ‘Year-to-date total’

Step 4: By default your base value will already be ‘Total Sales’ as that is what we selected in step 1.

Step 5: For our ‘Date’, we will just add ‘Date’ from the Calendar table on the right, and drag & drop it into the ‘Date’ item on the left. Once done, click on OK.

Step 6: While there is nothing more to do here, as your measure is done, but I want you to take note of the structure and function used for this measure. For this measure it uses TOTALYTD (Total Year-to-Date). The formula logic is shown below:

total ytd func

The Expression is where you place the field you want to measure, i.e. ‘Total Sales’. The Dates column is from your Calendar table. There is also the ability to filter the values before it gives you the answer. Finally you are also able to specify the ‘YearEndDate’ to ensure it caters for the different financial year end dates for the various industries – neat isn’t it?! Besides TOTALYTD (Total Year-to-date), Power BI DAX also has a function for TOTALQTD (Total Quarter-to-date) and TOTALMTD (Total Month-to-date).

Now lets move on to our last report…

Total Sales (Running Total)

jake running total

Step 1: Our basis for this calculation again is our measure called ‘Total Sales’. Right click on the ‘Total Sales’ measure in the ‘Fields’ Pane.

Step 2: From the pop-up box, select ‘Quick Measures’.

Step 3: Another pop-up box will appear with your quick measure variables. Select ‘Running total’

Step 4: By default your base value will already be ‘Total Sales’ as that is what we selected in step 1.

Step 5: For our ‘Date’, we will just add ‘Date’ from the Calendar table on the right, and drag & drop it into the ‘Date’ item on the left. Once done, click on OK.

Step 6: Choose whether you would like to see the data in ascending or descending order.

Step 7: While there is nothing more to do here, again take note of the structure and functions used for this measure. I’m not going to go into deep explanation of this measure, but the logic here is that it calculates the running total of ‘Total Sales’ by summing the ‘Total Sales’ for each period from the beginning (or depending on your filter).

Below we can see the three measures we created – time for some visuals!!

measures 1

 

 

 

 

I’m not taking you through each step in creating the report, but will highlight what needs to go where. Let’s add a line chart for the Total Sales measure:

jake report

Step 1: Select the line chart from the Visualizations pane.

Step 2: Add ‘Date’ from the Calendar table in the ‘Axis’ field and ‘Total Sales’ from the Sales table on the ‘Values’ field.

Step 3: Drill Down the Date Hierarchy by clicking on the ‘Expand all down one level in the hierarchy’ button until you get to the desired level, i.e. Year, Quarter, Month or Day.

Step 4: Continue to further format your chart until it looks as beautiful as this one. For tips on how to add the markers, please see our blog Power BI Desktop Reporting Updates: June 2017

Now follow the same steps for the other measures and format your report to match your company branding, colors etc. Below is Jake’s report which gives him more joy than puppies, ice cream and unicorns!!

jake report final

So there you have it……another exciting Power BI adventure, packed full of stunning visualizations and quick measures – which empowers the average Jake (or Joe) into Power BI Super Heroes!!

Stay tuned, we’ll be back again soon for more Power BI awesomeness!!

Until then…..Stay blessed!!

By | 2017-11-20T20:40:44+00:00 June 15th, 2017|Microsoft Power BI, Power BI Tips|0 Comments

About the Author:

Llewellyn is a Data Bear Consultant with over 10 years experience in Business Consulting / Analysis and Business Intelligence Application Development. His service offering is one that builds on the client’s needs, from revising business processes all the way through to the development of applications that will support the organisation’s operations and decision making requirements.

Leave A Comment