Forum Discussion

boclifton's avatar
boclifton
Regular Visitor
9 years ago
Solved

Burndown chart for data progress

I've found a few instances of using a burndown chart in TFS and Scrum settings, but my team has an existing report in SSRS that I'm trying to remodel in Power BI that utilizes a burndown chart for project progress for data migration.  I have several projects in a Projects table, and all the transaction data in a Transactions table for migrated data.  This leads me to two questions:

 

1) I have the expected number of items in the Projects table and the total number of items completed in the Transactions table.  Do I need to create a new table with calculated data? If so, will this account for slicing to different projects?

 

2) I also need to show the estimated date of completion based on an average of the Actual data migrated over the past 2 weeks.  How will I calculate this? 

 

Thanks in advance for any help.  I'm still pretty new to Power BI so a lot of this escapes me for now.  I've included an example of the current chart below.

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi boclifton,

     

    You can try to use below formulas to get the remain item and bytes.

     

    Measures:

    Remain Items =
    var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
    var TotalItem=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionItemsMigrated]) + MAX(Table[CurrentItemFailed]) //total items + failed items
    return
    TotalItem-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionItemsMigrated])

     

    Remain Bytes =

    var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
    var TotalBytes=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionSizeBytes])
    return
    TotalBytes-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionSizeBytes])

     

    Regards,

    Xiaoxin Sheng

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi boclifton,

     

    You can refer to below sample to create the visual on power bi desktop.

     

    Table:

     

    Measure:

    Date Range(Day) = COUNTROWS(ALL(Sheet1)) //deadline

     

    Actural Line =
    var total= MAXX(ALL(Sheet1),[Item Remain])
    var unit= total/[Date Range(Day)]
    var DateRank=FILTER(ALL(Sheet1),Sheet1[Date]<MAX(Sheet1[Date]))
    return
    if(total- COUNTX(DateRank,[Date])*unit>0,total- COUNTX(DateRank,[Date])*unit,0)

     

    Create visual:

     

     

    You can also try to use trend line.

     

     

    Regards,

    Xiaoxin Sheng

    • boclifton's avatar
      boclifton
      Regular Visitor
      9 years ago

      This is great, Anonymous!  Thank you!  However, I don't have it working quite yet because I need a little clarification ...

       

      The sample table you reference (Sheet1) is a list of numbers I don't have available.  I've included two samples from my Transactions data.  Since my Transactions table contains data from several projects, can you guide me in getting the Item Remaining column?  Is there a way I can make a calculated table to get this data?  The total items for the project is held in a separate table called Projects, so I'm not sure how to make that work.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        9 years ago

        Hi boclifton,

         

        You can try to use below formulas to get the remain item and bytes.

         

        Measures:

        Remain Items =
        var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
        var TotalItem=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionItemsMigrated]) + MAX(Table[CurrentItemFailed]) //total items + failed items
        return
        TotalItem-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionItemsMigrated])

         

        Remain Bytes =

        var currProject=LASTNONBLANK(Table[ProjectID],[ProjectID])
        var TotalBytes=SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject),[TransactionSizeBytes])
        return
        TotalBytes-SUMX(FILTER(ALL(Table),Table[ProjectID]=currProject&&Table[Date]<=MAX(Table[Date])),[TransactionSizeBytes])

         

        Regards,

        Xiaoxin Sheng

    • MPNZ's avatar
      MPNZ
      Frequent Visitor
      5 years ago

      Hi there

       

      When I try your initial solution, my DateRank measure is not passing and it is providing me this error "The expression refers to multiple columns. Multiple columns cannot be converted to a scalar value." . I have only referenced one date column, and have tried other columns without success...can you please tell me why else this error may occur?

       

      Thanks

      Megan