Best approach to obtain different level of averages

[Anonymous]

Hello everyone,

I'm a long time Excel user, rather at advanced level, however am rather new to PowerBI. I have been playing around my data but I am unsuccesful at obtaining what I need. I am truly sorry to be this demanding but I have stumbled upon my limit.

Context:

• I am building a Dashboard to measure our performance/poductivity
• It is realted to project management and based on Sprints

What I need:

• Calculate the average productivity per person per sprint
• Calculate the team average productivity per sprint
• Calculate the best performer per sprint
• Calculate the global average productivity of all time
• Calculate the best performer of all time
• We measure the productivity based on the Sum of Weight of Tasks / Quantity of Days Worked in the sprint

Data Structure:

• Main source of information (contains most relevant information)
• Working Days = a separate table that has the worked days by matching Assignee name and Sprint
• Attached is a Pbix with my attempts to make it work and also a clean Pbix if anyone has a better approach.
  • PBI Folder with both files

What would be the best approach for this?

Many thanks to any help you can provide.

[ERD · Accepted solution]

Hi @Anonymous,

I guess there are multiple options, but you can try the next approaches:

average_productivity = 
VAR _weight = SUM ( Data[Weight] )
VAR c_sprint = SELECTEDVALUE ( Data[Sprint Name] )
VAR w_days = CALCULATE ( SUM ( WorkingDays[Working Days] ), WorkingDays[Sprint] = c_sprint )
RETURN
    IF ( HASONEVALUE ( Data[Sprint Name] ) && HASONEVALUE(WorkingDays[Assignee Name]), _weight / w_days )

average_productivity_total = 
VAR empl_amt = DISTINCTCOUNT ( WorkingDays[Assignee Name] )
RETURN
    SUMX ( Data, [average_productivity] ) / empl_amt

best_performance = 
VAR t =
    ADDCOLUMNS (
        SUMMARIZE ( WorkingDays, WorkingDays[Assignee Name], WorkingDays[Sprint] ),
        "avgProductivity",
            VAR c_employee = CALCULATE ( SELECTEDVALUE ( WorkingDays[Assignee Name] ) )
            VAR c_sprint = CALCULATE ( SELECTEDVALUE ( WorkingDays[Sprint] ) )
            VAR _weight = CALCULATE (
                    SUM ( Data[Weight] ),
                    Data[Task Assignee Name] = c_employee,
                    Data[Sprint Name] = c_sprint
                )
            VAR w_days = CALCULATE ( SUM ( WorkingDays[Working Days] ), WorkingDays[Sprint] = c_sprint )
            RETURN
                _weight / w_days
    )
RETURN
    MAXX (
        FILTER ( t, [Sprint] IN VALUES ( Data[Sprint Name] ) ),
        [avgProductivity]
    )

average_productivity_all_time = CALCULATE( [average_productivity_total], ALL(Data[Sprint Name]))

best_performer (all time) = 
VAR t =
    ADDCOLUMNS (
        SUMMARIZE ( WorkingDays, WorkingDays[Assignee Name], WorkingDays[Sprint] ),
        "avgProductivity",
            VAR c_employee = CALCULATE ( SELECTEDVALUE ( WorkingDays[Assignee Name] ) )
            VAR c_sprint = CALCULATE ( SELECTEDVALUE ( WorkingDays[Sprint] ) )
            VAR _weight = CALCULATE (
                    SUM ( Data[Weight] ),
                    Data[Task Assignee Name] = c_employee,
                    Data[Sprint Name] = c_sprint
                )
            VAR w_days = CALCULATE ( SUM ( WorkingDays[Working Days] ), WorkingDays[Sprint] = c_sprint )
            RETURN
                _weight / w_days
    )
var maxV = MAXX ( t, [avgProductivity])
RETURN
    MAXX ( FILTER(t, [avgProductivity] = maxV), [Assignee Name] )

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

I am a Ukrainian living in Ukraine. Please, help us to survive! Please, Ask your government to react!
Here are official ways you can support us financially (accounts with multiple currencies):
https://bank.gov.ua/ua/about/support-the-armed-forces

USD:
BENEFICIARY: National Bank of Ukraine
BENEFICIARY BIC: NBUA UA UX
BENEFICIARY ADDRESS: 9 Instytutska St, Kyiv, 01601, Ukraine
ACCOUNT NUMBER: 400807238
BENEFICIARY BANK NAME: JP MORGAN CHASE BANK, New York
BENEFICIARY BANK BIC: CHASUS33
ABA 0210 0002 1
BENEFICIARY BANK ADDRESS: 383 Madison Avenue, New York, NY 10017, USA
PURPOSE OF PAYMENT: for crediting account 47330992708

Accounts details for other currencies (EUR|GBP|CHF|AUD|CAD|PLN) can be found here: https://bank.gov.ua/ua/about/support-the-armed-forces

[Anonymous]

This helped me work on the right direction, had to make a few adjustments to some measures to find the right behaviour with filters. Amazing!!

[Anonymous]

Hi @amitchandak ,

Just tried it on several levels and obtained the following message:

The True/False expression does not specify a column. Each True/False expressions used as a table filter expression must refer to exactly one column.

[amitchandak]

@Anonymous ,share measure to check

[Anonymous]

@amitchandak 

Productivity =
sum(Data[Weight])/
calculate(sum(WorkingDays[WorkingDays]);
ISINSCOPE(WorkingDays[Sprint]);
ISINSCOPE(WorkingDays[Working Days]))

[amitchandak]

@Anonymous , I think you need to use isinscope

https://www.kasperonbi.com/use-isinscope-to-get-the-right-hierarchy-level-in-dax/