3 easy steps to calculate Running Total in Power BI
Power BI is a platform that allows you to connect to various data sources, transform and model your data, and create interactive dashboards and reports. Power BI can help you gain insights from your data and communicate them effectively.
What is Running Total?
A running total, also known as a rolling total or partial sum, is the summation of a sequence of numbers which is updated each time a new number is added to the sequence. This is done by adding the value of the new number to the previous running total. It represents the total number or amount of a list of things that changes as you add or subtract things from the list.
Creating the table with Running Total
We will be making this table having the Running Total:
As you can see for a year, the Sum of Sales get added for all the years prior to it, giving us the running total up to that year i.e. for the year 2020 we are adding 4,94,040.21 and 4,72,993.03 and we are getting 9,67,033.24 which is the running total for the year.
But first we need to create a calendar table because there is a requirement of creating a calendar table whenever we are dealing with dates in Power BI.
1.DAX for calendar table creation:
Calender = CALENDAR(MIN(Orders[Order Date]), MAX(Orders[Order Date]))
where the arguments are the start and end dates in the main table which here is Orders and the MIN() and MAX() functions give us the min and max dates in Orders table which are nothing but our start and end dates.
Then this calendar table needs to be connected to the main table in data modelling section of Power BI. Just drag Date column in calendar table to the respective date column in main table. Refer this article for connecting the tables, it is very easy to do.
Also we need to extract Year from the calendar table for which head to table view select the calendar table which here is Calender, add a New Column from the panes in the top and use this DAX:
Year = YEAR(Calender[Date])
2.DAX for Rolling Total calculation:
Running Total = SUMX(FILTER(ALL(Orders), Orders[Order Date]<=MAX(Orders[Order Date])), Orders[Sales])
Now SUMX needs a Table and an Expression:
- For Table part, first we use a Filter function which itself requires a table and we are giving it ALL(Orders) to ignore any filters that might have been applied outside this formula with Orders being our main table and for the condition we give Orders[Order Date]<=MAX(Orders[Order Date]) to include all dates which are less than or equal to the max date in the given context.
- For Expression we give the metric for which we want the sum calculated, here it being Orders[Sales].
What SUMX does is that it creates a row context for each row in the table, evaluates the expression and then sums the results.
Here each row is represented by the Year, and the FILTER function filters all the dates which are less than or equal to the max date for each year encountered, then the SUMX gets this information of dates and creates a row context for every year encountered as it iterates through the rows and keeps on summing up the Orders[Sales] i.e. suppose if we are 2019 then it will give us the Sum of Sales from first date to last date of 2019, if we are in 2020 it will give use sum from first date of 2019 to last date of 2020 and so on.
3.Creating the table:
Now we need to get a table from visualizations and add Year from Calendar and Sum of Sales, Running Total from Orders.
Finally in Visualizations go to Format your visual then to Visual expand Style presets and select Contrast alternating rows and we are done.
To create a KPI with a unit refer to this link.
To create a Rolling 12 Months KPI refer to this link.
To create a Dynamic Title refer to this link.
To create a Concatenated KPI refer to this link.
To read more stories like this you can follow me with this link.
References:
Don’t forget to subscribe to
and join our Power BI community
