Multiple Running Total Line Chart

[Anonymous]

I have a data set containing information used for reporting planned .vs. actual for a scrum burnup chart.

The pertainent columns consist of:

• Sprint Planned - The sprint in which a user story is intended to be worked
• Points Planned - The point value of the user story.
• Sprint Closed - The sprint in which the user story was actually completed
• Points Closed - The point value of the user story.

Sample data:

I have also added Running Total quick measures for Points Planned and Points Closed.

When attempting to graph the Running Totals for Points Planned and Points Closed against Sprint Planned and Sprint Closed respectively, I am seeing that only the Planned series is displayed as a running total.  The Closed series is displayed as totals by Sprint.:

Line ChartChart Settings

It appears that the issue is related to the multiple values applied to the axis.  The data values in each axis series are of a common fixed set, but since they are contained in different series, it prevents different values from being plotted on a common axis.

My expectation was that the Closed line would be like:

Expected Chart

My initial workaround was to overlay separate line charts - each plotting points over sprints, but the problem then becomes that the overlayed charts do not scale uniformly over time.

Any suggestions or ideas would be greatly appreciated.

Thank you in advance.

Ross

[Anonymous · Accepted solution]

The answer to this problem was identified by a collegue through an internal support forum.  In the interest of sharing, the following post contains the solution, which yields:

Final Solution

===================================================================================

The solution to your problem is add a new 'Sprint' table that contains a distinct list of sprints.  You can then link this table to both the Sprint Planned and Sprint Closed columns.  This will allow you to use this new table as the shared axis between the planned and closed running total of points.

After importing the new 'Sprint' table, you will want to create 2 relationships between the 'Planned vs Closed' table and the 'Sprint' table.  One relationship should link the Planned Sprint column to the Sprint table, and the other should link the Closed Sprint column to the Sprint table.  Note that one of these two relationships will be a hashed line instead of a solid line.  The hashed line means that the second relationship is inactive.  More on this later.

After creating the relationships, you can create a new Line Chart.  Make sure to use the column from the 'Sprint' table as the axis.  You can add the Points Planned and Points Closed to the Values, and create the 2 running value quick measures.

Since Power BI only allows one relationship between 2 tables to be active at once, you will need to slightly change one of the measures.  After being created, both measures will be using the active relationship.  This means one of the running total measures will be incorrect when it is first created.  For example, if the Sprint Planned relationship is the active one, the Points Closed measure will be using the Planned Sprint sprint names to sum.

To change this, you need to force this incorrect measure to use the inactive relationship rather than the active one.  You can do this by changing the DAX of the incorrect measure.  In my case, the Points Closed was the incorrect measure (summing by Planned Sprint).  The initial DAX created by the quick measure was as follows:

Points Closed running total in Sprint = 

CALCULATE(

SUM('Plan vs Closed'[Points Closed]),

FILTER(

ALLSELECTED('Sprint'[Sprint]),

ISONORAFTER('Sprint'[Sprint], MAX('Sprint'[Sprint]), DESC)

)

)

To force the measure to use the inactive relationship, I had to add the USERELATIONSHIP function, as follows:

Points Closed running total in Sprint = 

CALCULATE(

SUM('Plan vs Closed'[Points Closed]),

FILTER(

ALLSELECTED('Sprint'[Sprint]),

ISONORAFTER('Sprint'[Sprint], MAX('Sprint'[Sprint]), DESC)

),

    USERELATIONSHIP('Plan vs Closed'[Sprint Closed],Sprint[Sprint])

)​

After adding the USERELATIONSHIP function, the Points Closed measure correctly summed over Sprint Closed.

This should give you the expected chart you are looking for.

Let me know if you have an issues/questions.

Thanks,

Alex

[v-huizhn-msft]

Hi @Anonymous,

From what I know, the issue is not caused by the quick measure. Please create a two line chart visuals, one display the Running Totals for Points Planned against Sprint Planned, and another one displays the Running Totals for Points Closed and Sprint Closed respectively. If that works, it will be caused by mutiple axis fields.

If this still does not resolve the problem, do you mind share more details or sample data for further analysis?

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft Angelia,

Thank you for taking the time to respond.

As you suspect, the independent graphing of running total to sprint yields the expected results:

Planned RT Points .vs. Planned SprintClosed RT Points .vs. Closed Sprint

Since the Planned and Closed Sprints are not always the same, it is not possible to plot both series over a common axis.

[v-huizhn-msft]

Hi @Anonymous，

After test and research, it is impossible to plot both series over a common axis. Because the measure will be effected by the axis value. Thanks for understanding.

Best Regards,
Angelia

[Anonymous]

@v-huizhn-msft As documented in my posted solution, the limitation you identified is resolved by normalizing the relationship to a common value.  The measures can then be defined in terms of the normalized reference data.

Normalized Axis Data

[v-huizhn-msft]

Hi @Anonymous,

Got it. I learned new things. Thanks for sharing.

Best Regards,
Angelia