Skip to main content
cancel
Showing results for 
Search instead 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

Reply
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?

 

DateCrew#WR#
2/19/202011000
2/19/202021001
2/19/202031002
2/19/202041003
2/19/202041004
2/19/202051005
2/19/202051006
2/19/202061007
2/19/202071008
2/19/202081009
2/19/202091010
2/19/202091011
2/19/2020101012
2/19/2020111013
2/19/2020121014

 

1 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.

View solution in original post

4 REPLIES 4
harshnathani
Community Champion
Community Champion

@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)

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

Helpful resources

Announcements
Europe Fabric Conference

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 Carousel June 2024

Power BI Monthly Update - June 2024

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

RTI Forums Carousel3

New forum boards available in Real-Time Intelligence.

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