Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
aykim101
Microsoft Employee
Microsoft Employee

help with burndown chart

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

 

DateCountBurndown
Start0133
05/29/171132
05/31/172130
06/01/171129
06/02/172127
06/07/171126
06/09/172124
06/12/172122
06/14/171121
06/15/171120
06/16/171119
06/23/171118
06/26/175113
06/27/172111
06/28/173108
06/30/176102
07/03/171101
07/04/171100
07/05/171387
07/06/17483
07/07/17677
07/08/17275
07/09/17273
07/10/17172
07/11/17171
07/12/17170
07/13/17268
07/24/17464
07/25/17163
07/31/17360
08/14/17159
10/31/17158
2 ACCEPTED SOLUTIONS

@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.

 

Capture.JPG

 

Cheers,

 

Sam

 

 

// If this is a solution please mark as such

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

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

// if this is a solution please mark as such. Kudos always appreciated.

View solution in original post

9 REPLIES 9
v-qiuyu-msft
Community Support
Community Support

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])

 

w1.PNGw2.PNGw3.PNG

 

Best Regards,
Qiuyun Yu

 

 

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

@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.

 

Capture.JPG

 

Cheers,

 

Sam

 

 

// If this is a solution please mark as such

// if this is a solution please mark as such. Kudos always appreciated.

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

// if this is a solution please mark as such. Kudos always appreciated.

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

 

 

samdthompson
Memorable Member
Memorable Member

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

// if this is a solution please mark as such. Kudos always appreciated.
MFelix
Super User
Super User

Hi @aykim101,

Is the burndown a column on your data or do you want to calculate it in order to use it?

If it is a column just add it to your visual and should give you what you want.

MFelix

Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



aykim101
Microsoft Employee
Microsoft Employee

the 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


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



Helpful resources

Announcements
July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

Top Solution Authors