Per Capita Count

[samantha92]

I have a form that my maintenance department submits with each job they do, and those results flow into a Power BI report. I am trying to capture participation rates by craft based on each craft's population. For example, I have 2 insulators. If they submit 30 forms, that's 15 per person. Further, there's 6 electricians, if they submitted 12 forms, it's only 2 per person. I was able to get it for the whole data set, but I need it to be filterable by date, or at a minimum, by month.

Currently the craft is populated on the form, and in the data set when it's uploaded. I've added a column for "Number Submitted" that just puts "1" in each cell down the column. The headcount populates based on the craft text.

My end goal is a bar graph showing each craft's participation rate, filterable by date.

[Ashish_Mathur]

Hi,

Assuming each row is one form submission, try this measure

=COUNTROWS(Data)/MIN(Data[Headcount])

Drag Craft to the Table visual.

[Greg_Deckler]

Making some assumptions here but I think something along the lines of:

Measure = 
VAR __craft = MAX('Table'[Craft]) //Assuming Craft is used as a legend/axis
VAR __table = SUMMARIZE('Table',[Craft],"__people",AVERAGE([Headcount]),"__submitted",SUM([Number Submitted]))
VAR __table1 = ADDCOLUMNS(__table,"__average",[__submitted]/[__people])
RETURN
MAXX(FILTER(__table1,[Craft] = __craft),[__average])

I coded that blind so mileage may vary.