Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Thanks in advance for the help...
Trying to create a burndown line chart based on the sample data below
i cannot get the running total calculations to render the view.
the view would have the line chart start at 133 on 5/28 and trend downward to 58 by 10/31/17
Date | Count | Burndown |
Start | 0 | 133 |
05/29/17 | 1 | 132 |
05/31/17 | 2 | 130 |
06/01/17 | 1 | 129 |
06/02/17 | 2 | 127 |
06/07/17 | 1 | 126 |
06/09/17 | 2 | 124 |
06/12/17 | 2 | 122 |
06/14/17 | 1 | 121 |
06/15/17 | 1 | 120 |
06/16/17 | 1 | 119 |
06/23/17 | 1 | 118 |
06/26/17 | 5 | 113 |
06/27/17 | 2 | 111 |
06/28/17 | 3 | 108 |
06/30/17 | 6 | 102 |
07/03/17 | 1 | 101 |
07/04/17 | 1 | 100 |
07/05/17 | 13 | 87 |
07/06/17 | 4 | 83 |
07/07/17 | 6 | 77 |
07/08/17 | 2 | 75 |
07/09/17 | 2 | 73 |
07/10/17 | 1 | 72 |
07/11/17 | 1 | 71 |
07/12/17 | 1 | 70 |
07/13/17 | 2 | 68 |
07/24/17 | 4 | 64 |
07/25/17 | 1 | 63 |
07/31/17 | 3 | 60 |
08/14/17 | 1 | 59 |
10/31/17 | 1 | 58 |
Solved! Go to Solution.
@v-qiuyu-msft, I dont think that line looks quite right. Using the measure I outlined above, the result looks more like a burndown series. I have overlaid the two options for @aykim101 to see:
The line needs to continually drop from 133 by the increments of the count column to end at 58 on 31 October. Make sure that Date column is formatted as a date.
Cheers,
Sam
// If this is a solution please mark as such
Hi, as @MFelix and I alluded to, there needs to be a context for the calculation to take place. The calculations I outlined were in lieu of having a definned 133 so start from. You will need a table with the starting point for each of the burndown eg:
Project | Start point
Project 1 | 133
Project 2 | 150
...
Project n | value
Once you have that then and a relationship between the Project column and the Project column in your data table, you will be able to have the startpoint in your caluclation based on selection eg:
SOLVE WITH MEASURE:
Measure1:
Project Start point measure = FIRSTNONBLANK('Project Table'[Start Point],1)
Measure 2:
[Project Start point measure]-CALCULATE( SUM('Table1'[Count]), FILTER(ALLSELECTED('Table1'[Date]),ISONORAFTER('Table1'[Date], MAX('Table1'[Date]),DESC)))
Perhaps theres more to the origional post? Is the count column of the origional steps meant to sum to 133? If so then you could run a sum of the count column and use that as the starting point. =CALCULATE(SUM(Table1[COUNT]),ALL(Table1[DATE])) and use in lieu of the 133 in the calculation.
Cheers,
Sam
// If this is a solution please mark as such
Hi @aykim101,
You can replace Start with "5/28/2017" and add a index column in Query Editor, then create a calculated column like below:
Burn = var Pre = LOOKUPVALUE(Table1[Count],'Table1'[Index],'Table1'[Index]-1)
return
IF(Pre=2,'Table1'[Burndown]-2,'Table1'[Burndown])
Best Regards,
Qiuyun Yu
@v-qiuyu-msft, I dont think that line looks quite right. Using the measure I outlined above, the result looks more like a burndown series. I have overlaid the two options for @aykim101 to see:
The line needs to continually drop from 133 by the increments of the count column to end at 58 on 31 October. Make sure that Date column is formatted as a date.
Cheers,
Sam
// If this is a solution please mark as such
thank you all for the responses but i still cannot get it to work.... so frustrating.... 😞
is there a way to sum the total of records 133 and then run the burn down?
i tried both methods and i cannot get it to work
Hi, as @MFelix and I alluded to, there needs to be a context for the calculation to take place. The calculations I outlined were in lieu of having a definned 133 so start from. You will need a table with the starting point for each of the burndown eg:
Project | Start point
Project 1 | 133
Project 2 | 150
...
Project n | value
Once you have that then and a relationship between the Project column and the Project column in your data table, you will be able to have the startpoint in your caluclation based on selection eg:
SOLVE WITH MEASURE:
Measure1:
Project Start point measure = FIRSTNONBLANK('Project Table'[Start Point],1)
Measure 2:
[Project Start point measure]-CALCULATE( SUM('Table1'[Count]), FILTER(ALLSELECTED('Table1'[Date]),ISONORAFTER('Table1'[Date], MAX('Table1'[Date]),DESC)))
Perhaps theres more to the origional post? Is the count column of the origional steps meant to sum to 133? If so then you could run a sum of the count column and use that as the starting point. =CALCULATE(SUM(Table1[COUNT]),ALL(Table1[DATE])) and use in lieu of the 133 in the calculation.
Cheers,
Sam
// If this is a solution please mark as such
probably needed to be more specific
small example
project | date
xyz | 5/23/17
abd | 5/30/17
das | 6/15/17
qop | 7/15/17
burndown total = 4
4 on the min date-1 (5/22)
5/23 3
5/30 2
6/15 1
7/15 0
Hi,
You can do this either by calculated column or measure:
first make sure your date column is a date and that the row currently defined as 'start' has a date in it (You cant have multiple data types in one column). For simplicity when testing I set it as 05/01/2017.
SOLVE BY COLUMN:
133-CALCULATE(sum(Table1[Count]),FILTER(Table1,Table1[Date]<=EARLIER(Table1[Date])))
SOLVE WITH MEASURE:
133-CALCULATE( SUM('Table1'[Count]), FILTER(ALLSELECTED('Table1'[Date]),ISONORAFTER('Table1'[Date], MAX('Table1'[Date]),DESC)))
You can make the starting point dynamic with say a related table of different projects starting points but I am unsure how your model works.
Cheers
Sam
// If this is a solution please mark as such
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêsthe column values need to be calculated
each line item has a date that would represent "1" so if there are 2 line items happening on the same day, the burn down chart would reduce by 2 after that day
thanks
HI @aykim101,
How do I know that the burndown starts at 133, how do you define the starting point of the burndown?
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUser | Count |
---|---|
98 | |
91 | |
84 | |
73 | |
67 |
User | Count |
---|---|
115 | |
102 | |
98 | |
71 | |
66 |