cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
samantha92 Frequent Visitor
Frequent Visitor

Per Capita Count

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.

 

Capture.PNG

 

2 REPLIES 2
Super User
Super User

Re: Per Capita Count

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.


Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!


Super User
Super User

Re: Per Capita Count

Hi,

 

Assuming each row is one form submission, try this measure

 

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

 

Drag Craft to the Table visual.