Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I have a data set containing information used for reporting planned .vs. actual for a scrum burnup chart.
The pertainent columns consist of:
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.:
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:
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
Solved! Go to 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:
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:
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
@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:
Since the Planned and Closed Sprints are not always the same, it is not possible to plot both series over a common axis.
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
@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.
Hi @Anonymous,
Got it. I learned new things. Thanks for sharing.
Best Regards,
Angelia
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |