Power BI Running Total

Running Total in Power BI

Power BI seems to be a little difficult for MS Excel users because in excel we work cells, so by using cell references we can work easily but when it comes to Power BI we no longer work with cells but with entire columns and rows. This is where beginners in Power BI face huge challenges. One of the requirements of users in Power BI how do we get the running total or cumulative total of values. In this article, we will show you how to write DAX formulas to get running total in Power BI.

What is Running Total in Power BI?

Running total is the summation of values of each period before arriving at the overall period totals. For example, look at the below data in excel worksheet.

This is the daily sales numbers, so the running total is arriving each period number with previous period numbers. For the above data, we can apply the running total like below.

First running total is the total of first two days i.e. 341 + 769 = 1110, next it will add three days numbers 341 + 769 + 777 = 1887. This, in the end, we get to know the pattern of each day.

Examples of Running Total in Power BI

Below are examples of the running total in Power BI. You can download the workbook to use the same file as we used in this example.

Step #1: Similar stuff can be arrived in Power BI as well but not as easy as in excel, use the same data as shown above in Power BI as well.

Step #2: For this table, we can arrive running totals in three ways, first, we will arrive through “New Measure”, right-click on the table and choose “New Measure”.

Step #3: Name the measure as “RT Measure”. (RT= Running Total).

Step #4: Open the CALCULATE function first.

Step #5: The kind of Expression that we need to do with the CALCULATE function is “Summation of Sales Value”, so open SUM function and choose the “Sales” column.

Step #6: After applying the kind of calculation to be done next we need to apply the filter to decide the criteria to be matched for calculation. Open FILTER function now.

Step #7: Before we apply FILTER first we need to release any kind of filter applied to the “Date” column, so open ALL function to remove the filter from the “Date” column.

Step #8: In this function choose the Table or Column Name for which we need to remove the filter for, so choose the “Date” column.

Step #9: Once the filter is removed then we need to apply fresh filter criteria in Filter Expression, so for this again choose the date column.

Step #10: Once the “Date” column has been selected we need to apply the kind of filter to be applied, for this, we need to decide the last date in the “Date” column, so enter the logical operator as less than (<) and open MAX function.

Step #11: MAX function will find the last date in the column of “date” so supply date column.

Step #12: Ok, we are done close three brackets and hit enter key to get the result.

Step #13: Now insert the table visually and add “Date” and “Sales” columns first.

Step #14: This is the overall summary, now add a newly created measure to the table to get the “Running Total” column.

There you go we have running total measure.

We can also create a running total by using another measurement technique as well, but this measure will give different sorts of results only.

Step #15: Name this measure as “RT Measure 1”.

Step #16: Open the CALCULATE function.

Step #17: As we did in the previous method we need to do a summation of the sales column, so open the SUM function and choose the “Sales” column to sum.

Step #18: This time for filter criteria we will use the DATESYTD function.

Step #19: Choose the “Date” column for this function.

Step #20: Close two brackets and hit enter key to complete the formula.

Step #21: Ok, now add this new measure to our existing table visual and see the result.

We have got two different sets of running totals. The first running total ends at the end of the year on 31^st December and the second running total started freshly from the new year date from 01^st Jan.

Note: Power BI dashboard file can also be downloaded from the link below and the final output can be viewed.

Things to Remember

• Always create running total of new measures only to get perfect results.
• If you DATESYTD it will give running total from 01^st Jan to 31^st Dec only, any dates after that will be started as a new running total from next year.

Recommended Articles

This has been a guide to Power BI Running Total. Here we learn how to write DAX formulas to get running total in Power BI along with examples using the DATESYTD function. You may learn more about Power BI from the following articles –

• Power BI vs Tableau
• Workspace in Power BI
• Themes in Power BI
• Create Pie Chart in Power BI