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

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