Pareto, Burn-down & Accumulating Trend Charts in Power BI

I’m managing an Agile team project using Microsoft Teams – the new project management platform integrated with Office 365.  Teams is a simple and useful project management tool but it’s new and light on features.  Using Power BI, we want to show the hourly task burn-down for each two-week sprint.  In JIRA and some other more mature project management platforms, the burn-down chart is a standard feature in the tool that shows the number of hours or story points remaining, compared to the estimated number for the sprint.  Just as I began working on that, a client asked for some help creating a Pareto chart and it occurred to me that burn-down and Pareto charts are very similar variations of the same type of chart presentation.  These are not so much chart types as they are a set of calculations and techniques for displaying a desired result.

Project Hours Burn-Down Chart

Here’s the Burn-down chart showing days of the sprint on the X-axis and the hours for all resources as columns.  The burn-down line represents the number of estimated hours remaining for the duration of the sprint as a percentage of the total estimated hours for all resources.

Here’s another variation of the burn-down chart using stacked columns for each project resource, in my example; developers named Marta, Rob and Vivek.  Again, the burn-down line shows the daily percentage of estimated hours remaining compared to the total hours that have been “burned” for all days up to and including the current day.

The columns in the chart are simply the sum of hours reported.  I didn’t even create a measure for this value.  It’s just a summable column in the ‘Project Hours’ table named “Hours”.  Here’s a look at the data, including the measures used in the chart:

…and finally, here is the DAX measure code used for the line part of the column/line combination chart:

Project Hours Remaining % =
VAR DayAccumHours =
CALCULATE( SUM( ‘Project Hours'[Hours] ),
FILTER( ALLSELECTED( ‘Day’ ),
‘Day'[Day Number] <= MIN( ‘Day'[Day Number] )
)
)
VAR ProjectHoursRemaining = SUM( ‘Sprint Estimates'[Est Hours] ) – DayAccumHours

RETURN
DIVIDE( ProjectHoursRemaining, SUM( ‘Sprint Estimates'[Est Hours] ) )

Pareto Chart

This style of chart is very similar but the key differences are that the columns are ordered by the contribution value in descending order.   You can see that my sample dataset just uses numbers for the categories (e.g. “8”, “2”, “4”, etc.) but these could just as easily be names of resources, sales people, customers or products.  The columns are in descending order of aggregate value rather a time series of the axis field value.  To order by measure or value (my aggregate field is literally called “Value”), set the Type property for the X-axis to “Categorical”.

Here’s the Pareto chart.  The contribution line shows the percentage of the total contribution that a category item and all of it’s predecessors in ranked descending order have made to that point.  For example, the first four top-ranking categories (8, 2, 4 and 5) account for about 50% of the total.

The DAX measure code for the accumulating percentage value used for the line, is as follows:

Accum Value % All =
VAR AccumValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series’ ), ‘Pareto Series'[Value] >= MIN( ‘Pareto Series'[Value] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM( ‘Pareto Series'[Value] ), ALLSELECTED( ‘Pareto Series’ ) )

RETURN
DIVIDE( AccumValue, AllSeriesTotal )

Accumulating Trend Series or Trend Chart

For good measure, I have one more example.  I actually started experimenting with calculation techniques using this chart.  It is essentially the burn-down chart example with the line presenting the opposite of the burn-down, so maybe this is a “burn-up chart” – I don’t know.  This may be useful when you want to visualize a time or other continuous series on the X-axis and the accumulating contribution as a line along with the actual contribution values as columns.

…and the measure DAX code used for the trend line:

Accum Series Value % All =
VAR AccumSeriesValue =
CALCULATE( SUM(‘Pareto Series'[Value] ),
FILTER( ALLSELECTED( ‘Pareto Series'[Period] ), ‘Pareto Series'[Period] <= MIN( ‘Pareto Series'[Period] ) )
)
VAR AllSeriesTotal = CALCULATE( SUM(‘Pareto Series'[Value] ), ALLSELECTED(‘Pareto Series’ ) )

RETURN
DIVIDE( AccumSeriesValue, AllSeriesTotal )

I’m hopeful that this will be useful and save you some time and energy.  As always, please leave comments and reach-out if you need some help.

You can download the example Power BI Desktop file here: Simple Pareto & Burn-down Chart Example.zip