Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Multiple Running Total Line Chart

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 in...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago
    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's avatar
v-huizhn-msft
Microsoft Employee
9 years ago

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's avatar
Anonymous
Not applicable
9 years ago

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's avatar
    v-huizhn-msft
    Microsoft Employee
    9 years ago

    Hi Anonymous,

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

    Best Regards,
    Angelia