Forum Discussion

Alicia_Anderson's avatar
Alicia_Anderson
Resolver I
4 years ago
Solved

Help with Averagex measure

Each Sprint we have a calculated Say Do%.  (Points Done/Points Committed).

 

I need to calculate what is a team's average Say Do is, excluding the current sprint.

Ex:  87%, 35%, 61%, 0% = Average should be 46%  

 

For some reason, my measure is showing 61% (it is not including 0%).  

SayDoAvg = Averagex(Summarize(Filter('PI_Work','PI_Work'[Sprint#] < [Current_Sprint#]),PI_Ref[Sprint]),[SayDo])
 
SayDo = Min(1,(DIVIDE('PI_Work'[Effort-Done_0],[Committed],0)))
 
Effort-Done_0 = VAR DoneZero = [Effort-Done]
return IF( SELECTEDVALUE( 'PI_Work'[Sprint#]) < [Current_Sprint#], COALESCE(DoneZero, 0), DoneZero)
 
Need Help
  • Anonymous's avatar
    Anonymous
    4 years ago

    Alicia_Anderson , great, I'm glad I solved your problem. Please accept my answer as the solution.

    If you have an additonal question, then please post another question in the forum.

     

6 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    4 years ago

    Alicia_Anderson , I assume the SayDoAvg visual is a Card visual. Have you checked if there are any unwanted filters on the SayDoAvg visual?

    • Alicia_Anderson's avatar
      Alicia_Anderson
      Resolver I
      4 years ago

      Yes, it is a card and no, there are no filters applied.  Sprint PI2-5 just completed and now the card shows the average is 55%.  It is definitely ignoring the 0% SayDo for PI2-4.    

       

      Someone helped me with a measure to get 0 to show when there were no points Done in a Sprint.  I should probably have included that.  

       

      Effort-Done_0 = VAR DoneZero = [Effort-Done]
      return IF( SELECTEDVALUE( 'PI_Work'[Sprint#]) < [Current_Sprint#], COALESCE(DoneZero, 0), DoneZero)
       
      The SayDo measure is defaulting to 0 on error.   How do I get the SayDoAvg measure to accept that 0?
    • Anonymous's avatar
      Anonymous
      Not applicable
      4 years ago

      Alicia_Anderson , Am I right in thinking that your table PI_Work contains no rows for Sprint PI2-4? If that is the case, then your SayDoAvg measure will never calculate anything for sprint PI2-4, because there are no rows for that sprint for the AVERAGEX to iterate through. I would suggest changing your SayDoAvg measure to iterate through the PI_Ref table like this:

       

       

      SayDoAvg = 
    AVERAGEX(
        FILTER('PI_Ref', 'PI_Ref'[Sprint] < [CurrentSprint]),
        [SayDo]
    )

       

       

       

      That gives me this result:

      Alternatively you could consider simply displaying the SayDo measure in the card visual, which will show 40% based on Total Work Done = 83 / Total Committed = 206. Taking an average of a percentage might not be what you want. For example, if sprints 1 thru 4 had WorkDone = 1 and Committed = 1, but sprint 5 had Committed = 100 and WorkDone = 5. Your SayDoAvg would work out at 81% (which sounds pretty good), whereas total WorkDone / Total Committed over those 5 sprints would be 9 / 104 = 9% (which doesn't look so good). It really comes down to what you want that SayDoAvg measure to communicate to the users.

       

      • Alicia_Anderson's avatar
        Alicia_Anderson
        Resolver I
        4 years ago

        Thank you.  Your suggestion worked and now displays the correct amount.   FYI...40% would NOT be correct because the percentage must be calculated based on the results of each Sprint, not the totals overall.   

         

        I have one additional problem I am trying to figure out.   I need the Total line for the AverageX measure to calculate based on the Average for each Team not the sum of Done/Committed.  

         

        In the example below, the Total SayDoAvg should be 83% (Average: 86, 98, 49,100).    Again, it is not correct to take the totals in this instance to calculate the average.  A team can never achieve more than 100% Say Do, even if they completed more than they committed.   Also, the Say Do must be calculated on a Sprint by Sprint basis.    Any assistance on this as well is greatly appreciated.