Forum Discussion

aykim101's avatar
aykim101
Microsoft Employee
9 years ago
Solved

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 th...
Need Help
  • samdthompson's avatar
    samdthompson
    9 years ago

    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

  • samdthompson's avatar
    samdthompson
    9 years ago

    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

samdthompson's avatar
samdthompson
Memorable Member
9 years ago

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