cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Daily Productivity of Crews - Sum of Work Completed / distinct crew

I'm trying to create a productivity dashboard that summarizes work completed by crew / day over a period of time.  For one day the calculation I use is count of WR# / distinct count of crews.  Is there a DAX I can use (sumx maybe?) or something else that will allow me to toggle between day, month or year and have an accurate result?

 Date Crew# WR# 2/19/2020 1 1000 2/19/2020 2 1001 2/19/2020 3 1002 2/19/2020 4 1003 2/19/2020 4 1004 2/19/2020 5 1005 2/19/2020 5 1006 2/19/2020 6 1007 2/19/2020 7 1008 2/19/2020 8 1009 2/19/2020 9 1010 2/19/2020 9 1011 2/19/2020 10 1012 2/19/2020 11 1013 2/19/2020 12 1014

1 ACCEPTED SOLUTION
Community Champion

Hi @Anonymous ,

You can try this.

Percentageee =

var Countwr =CALCULATE(COUNT('Table'[WR#]),FILTER(ALL('Table'),[Date]=MAX([Date])))
var COUNTCrew = CALCULATE(DISTINCTCOUNT('Table'[Crew#]),FILTER(ALL('Table'),[Date]=MAX([Date])))

RETURN

DIVIDE(Countwr,COUNTCrew)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Else pls share sample data in text form (not an image). If possible, you can share your .pbix file too.

4 REPLIES 4
Community Champion

@Anonymous ,

Create a Date Table.

Use the following measure.

Percentageee =

var Countwr =CALCULATE(COUNT('Table'[WR#]),ALLEXCEPT('Date','Date'[Date]))
var COUNTCrew = CALCULATE(DISTINCTCOUNT('Table'[Crew#]),ALLEXCEPT('Date','Date'[Date]))

RETURN

DIVIDE(Countwr,COUNTCrew)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Community Champion

Hi @Anonymous ,

You can try this.

Percentageee =

var Countwr =CALCULATE(COUNT('Table'[WR#]),FILTER(ALL('Table'),[Date]=MAX([Date])))
var COUNTCrew = CALCULATE(DISTINCTCOUNT('Table'[Crew#]),FILTER(ALL('Table'),[Date]=MAX([Date])))

RETURN

DIVIDE(Countwr,COUNTCrew)

Regards,
Harsh Nathani

Did I answer your question? Mark my post as a solution! Appreciate with a Kudos!! (Click the Thumbs Up Button)

Else pls share sample data in text form (not an image). If possible, you can share your .pbix file too.

Anonymous
Not applicable

Harsh,

The formula works great for daily productivity, but when I try to roll it up to month or year, I think it is adding all work completed and dividing by distinct count of crews for the entire time period.  Instead, I need it to take the time period chosen in the dashboard, sum all work completed for the month and divide by the sum of distinct crew count by day.

Thank you!

Anonymous
Not applicable

Your requirements are not totally clear... but you can try this:

// Dates is a Date table in the model.
// Joins to T on [Date] and slicing
// by [Date] taken from T is forbidden.
// The date column in T must be hidden.

[Productivity] =
// From the data you've posted it
// follows that each row has a different
// WK#, so COUNTROWS should be used.
var __workDone = COUNTROWS( T )
var __crewCount =
SUMX(
Dates[Date],
CALCULATE(
DISTINCTCOUNT( T[Crew#] )
)
)
var __result =
DIVIDE( __workDone, __crewCount )
return
__result

Best

D

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

#### New forum boards available in Real-Time Intelligence.

Ask questions in Eventhouse and KQL, Eventstream, and Reflex.

Top Solution Authors
Top Kudoed Authors