topic Re: Best approach to obtain different level of averages in DAX Commands and Tips

Best approach to obtain different level of averages

Anonymous — Mon, 07 Mar 2022 11:11:38 GMT

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.

Re: Best approach to obtain different level of averages

amitchandak — Mon, 07 Mar 2022 12:11:27 GMT

Anonymous , I think you need to use isinscope

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

Re: Best approach to obtain different level of averages

Anonymous — Mon, 07 Mar 2022 15:06:12 GMT

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.

Re: Best approach to obtain different level of averages

amitchandak — Mon, 07 Mar 2022 15:07:00 GMT

Anonymous ,share measure to check

Re: Best approach to obtain different level of averages

Anonymous — Mon, 07 Mar 2022 15:13:23 GMT

amitchandak

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

Re: Best approach to obtain different level of averages

ERD — Mon, 07 Mar 2022 17:16:49 GMT

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

Re: Best approach to obtain different level of averages

Anonymous — Thu, 10 Mar 2022 11:04:30 GMT

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!!