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

[Anonymous]

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

[harshnathani]

@Anonymous ,

Create a Date Table.

link Date Table (Date)  with  Date of your Crew 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)

[harshnathani · Accepted solution]

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]

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]

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