Running Total (Cumulative) Line Graph by Day

In the early stages of its life, Microsoft PowerBI still lacks quite a bit of basic functionality that one would find in other data visualization tools. One such functionality is the ability to quickly perform cumulative (or running total) functions. Fortunately, using the CALCULATE command, we are able to hack our way to a solution.

 

Begin by creating a NEW MEASURE. Right-click on the table that you want to add the measure to (or follow this tutorial if you still need help). Use the calculation template below:

CALCULATE (
SUM ( <value> ),
FILTER ( ALL ( <tablename> ), <group-by-field> <= MAX ( <group-by-field> ) )
)

My example pasted below:

measure

 

 

Now, add a line graph widget by clicking the line button in the visualizations menu.

lg widget

 

Now, add the new MEASURE that you created into the “Values” field and add the <group-by-field> into the “Axis” field of the tool.  In my example, I added “RunningTotal” to the “Values” field and “date” to the “Axis” field.

select

 

Voilà. Cumulative Line Graph.  I also like to add a “Total” card at the top, just to double-check that it worked.

result

 

Microsoft Power BI (or is it PowerBI with no spaces…I don’t even know) has the potential to be a threat to an already saturated BI Data Visualization market, but it has a long way to go, yet.

 

Leave a Reply

Your email address will not be published. Required fields are marked *