How to calculate averages by groups

[andrekroker]

Hi!

I´m new to PowerBI but thrilled to learn more! Consider the following data table


And the assumptions below:
   
   User Stories and Bugs contain Story Points.
   Tasks contain Completed Work Hours
   One User story or Bug may have 1 or more Tasks associated as sub-items (in other words: each task will have 1 User Story or Bug in the Parent Work Item Id field)

And I have to address these 2 questions:
   1. Sum of Completed Work for each User Story or Bug
   2. Sum of Completed Work in tasks whose User Story or Bug is closed / StoryPoints of 'Closed' User Stories or Bugs. (Average)

The solution is demonstrated in the last 2 columns.
How to make this work in PowerBI?

[v-gizhi-msft]

Hi,

Please try these two measures:

Total Completed Work per User Story(Measure) = 
CALCULATE (
    SUM ( 'Table'[Completed Work] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[Parent Work Item] IN FILTERS ( 'Table'[Work Item ID] )
    )
)

Average(Measure) = 
VAR a =
    CALCULATE (
        SUM ( 'Table'[Completed Work] ),
        FILTER (
            ALLSELECTED ( 'Table' ),
            'Table'[Parent Work Item]
                = CALCULATE (
                    MAX ( 'Table'[Work Item ID] ),
                    FILTER (
                        'Table',
                        'Table'[State] = "Closed"
                            && 'Table'[Work Item Type] IN { "User Story", "Bug" }
                    )
                )
        )
    )
VAR b =
    CALCULATE (
        SUM ( 'Table'[Story Points] ),
        FILTER (
            'Table',
            'Table'[State] = "Closed"
                && 'Table'[Work Item Type] IN { "User Story", "Bug" }
        )
    )
RETURN
    IF (
        ISINSCOPE ( 'Table'[.] ),
        IF (
            MAX ( 'Table'[Work Item Type] ) <> "Task",
            IF (
                MAX ( 'Table'[State] ) <> "Closed",
                MAX ( 'Table'[Work Item Type] ) & " is not closed",
                a / b & ""
            )
        ),
        a / b & ""
    )

The result shows:

See my attached pbix file.

Best Regards,

Giotto

[andrekroker]

@v-gizhi-msft 

Considering this set of data:

And taking just the Average(Measure), if I chose to return only 'VAR a' 
This is what I get:
MAX(Table[Work Item Id])

If I switch the MAX function for the MIN function, I get the opposite:
MIN(Table[Work Item Id])

And I don´t want neither the Min nor the Max. I want the Average to result the total of 16 (13 + 3) since both 24072 and 24075 are closed.

[v-gizhi-msft]

Hi,

Please try this 'Var a' in formula:

var t = ALLSELECTED ( 'Table' )
VAR a =
AVERAGEX (
    GROUPBY ( 'Table', 'Table'[Work Item ID] ),
    CALCULATE (
        CALCULATE (
            SUM ( 'Table'[Completed Work] ),
            FILTER (
                t,
                'Table'[Parent Work Item]
                    = CALCULATE (
                        MAX ( 'Table'[Work Item ID] ),
                        FILTER (
                            'Table',
                            'Table'[State] = "Closed"
                                && 'Table'[Work Item Type] IN { "User Story", "Bug" }
                        )
                    )
            )
        )
    )
)

Best Regards,

Giotto

[andrekroker]

Guys, thank you so much for the response!! I highly value your effort in answering my question.

@v-gizhi-msft regarding the first measure you wrote "Total Completed Work per User Story(Measure)" - I´ve verified it works fine except when I have 'Bugs' and 'Stories' filtered in the report.  How would you write this in order so this measure would fit in this scenario? 

PS: I´m also using a Sprint filter in order to get the amount of work completed on a given sprint. I´ve tried the ALL statement in place of the ALLSELECTED but it doesn´t give the correct result cause takes into account the entire data table (all sprints).

[v-gizhi-msft]

Hi,

If you want to filter [Work Item Type], please try to create this calculated column as a slicer:

TypeSlicer = 
IF (
    'Table'[Work Item Type] = "Task",
    CALCULATE (
        MAX ( 'Table'[Work Item Type] ),
        FILTER (
            'Table',
            'Table'[Work Item ID] = EARLIER ( 'Table'[Parent Work Item] )
        )
    ),
    'Table'[Work Item Type]
)

When select one value in slicer, it shows:

See my attached pbix file.

Best Regards,

Giotto

[andrekroker]

@v-gizhi-msft 
Well, at this moment I really don´t wanna add a slicer to filter Bugs and User Stories, my matrix is already filtered by Bugs and User Stories.
Any other way to accomplish this?

[v-gizhi-msft]

Hi,

What's your expected result?

Best Regards,

Giotto

[andrekroker]

@v-gizhi-msft 
The expected result would be a matrix in which values calculated by the measure created would fall into the column "HRS"

matrix
This matrix is filtered by sprint and Work Item Type (bugs and USer Stories). In the first column it shows the 'Work Item Id' and its title (elapsed)
Thank you in advance!

[v-gizhi-msft · Accepted solution]

Hi,

For this requirement, please take following steps:

1)Create two calculated tables:

Table 2 = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "ID", IF ( 'Table'[ID] <> 'Table'[Work Item ID], 'Table'[ID] ),
        "Type", 'Table'[TypeSlicer],
        "Sub", 'Table'[Work Item Type],
        "Com", 'Table'[Completed Work]
    ),
    [ID] <> BLANK ()
)

Table 3 = 
FILTER (
    SELECTCOLUMNS (
        'Table',
        "ID", IF ( 'Table'[ID] = 'Table'[Work Item ID], 'Table'[ID] ),
        "State", 'Table'[State],
        "Type", 'Table'[TypeSlicer],
        "Story Points", 'Table'[Story Points]
    ),
    [ID] <> BLANK ()
)

2)Create relationships based on [ID] columns:

3)Try to create this calculated column in Table 2:

HRS = 
IF (
    RELATED ( 'Table 3'[State] ) = "Closed",
    DIVIDE (
        CALCULATE (
            SUM ( 'Table 2'[Completed Work] ),
            FILTER ( 'Table 2', 'Table 2'[ID] = EARLIER ( 'Table 2'[ID] ) )
        ),
        IF (
            RELATED ( 'Table 3'[State] ) = "Closed",
            RELATED ( 'Table 3'[Story Points] ),
            0
        )
    ) & "",
    'Table 2'[Type] & " is not closed"
)

4)The result shows:

See my attached pbix file.

Best Regards,

Giotto

[andrekroker]

Dear @v-gizhi-msft 

Thank you so much for all support. At the start I´ve made 2 questions:

   1. Sum of Completed Work for each User Story or Bug
   2. Sum of Completed Work in tasks whose User Story or Bug is closed / StoryPoints of 'Closed' User Stories or Bugs. (Average)

For the 1st one, I think I found a solution. It´s very simple and is working so far. I´m using the column ID (you´ve created on your model), and then adding it into the table view (instead of plugging work item id in and filtering by 'User Stories' and 'Bugs').

For the 2nd one, I might struggle a bit more. When I create Tables 2 and 3 and connect to each other, I´m getting "many -to-many" relationships among other issues.

Thanks!

[Greg_Deckler]

I don't see a table. Please see this post regarding How to Get Your Question Answered Quickly: https://community.powerbi.com/t5/Community-Blog/How-to-Get-Your-Question-Answered-Quickly/ba-p/38490

But, this sounds like a measure aggregation problem. See my blog article about that here: https://community.powerbi.com/t5/Community-Blog/Design-Pattern-Groups-and-Super-Groups/ba-p/138149

[andrekroker]

Thanks for your reply @Greg_Deckler !

Yes, when I first published I noticed the picture wasn´t there. But now it´s being shown (at least for me). Just looked at your article, but the problem there seems a bit different from what I have here.

Thanks.

[Greg_Deckler]

Maybe:

Measure = 

  VAR __WorkItem = MAX('Table'[Work Item Id])

RETURN

  SUMX(

    FILTER(

      'Table',

      [Parent Work Item = __WorkItem

    ),

    [Completed Work]

  )

---