cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
calerof Regular Visitor
Regular Visitor

Measure per hour per person and overall team

Hello all,

 

I have a chart showing the volume processed by each user in the columns, and in the line I'm showing the average volume per hour. That works fine. What I need is to show in addition is a horizontal straight line with the overall average of volume per hour for the whole team.

 

I have a simple measure to calculate each individual's average volume per hour as follows:

 

Units per hour = DIVIDE(
			SUMX('Hoja de Trabajo', 'Hoja de Trabajo'[CONTAINERS_PROCESSED]),
			DISTINCTCOUNT('Hoja de Trabajo'[Hora])
)

 

My chart with this correct measure looks like this:

 

Avg_per_Hr_individual.pngAverage volumen per hour per each individual

 

But I'm trying to calculate the overall average of volumen per hour with the following code and shows wrong values:

 

Overall average per hour = DIVIDE(
				SUMX(ALLSELECTED('Hoja de Trabajo'),'Hoja de Trabajo'[CONTAINERS_PROCESSED]),
				DISTINCTCOUNT('Hoja de Trabajo'[Hora])
				)

 

Now with this measure my chart looks like follows:

 

Avg_per_Hr_team.pngOverall average of volume per hour for the whole team

The correct value for the team in this month is 4,443.08 units per hour. That value shows correctly in user # 1, 3, 4, 5. But the rest have strange figures that I don't understand where did they come from. 

 

Here's my data:

 

 

Data (in excel)

 

start_month2          
            
Sum of CONTAINERS_PROCESSEDUser #          
Row LabelsU1306535U1322541U1326259U1434227U1434234U1522978U1704125U1718016U1799142U1985457Grand Total
0695638451,1911,151 682   4,627
1440876601,0291,043 628   3,887
2143 258327253 108   1,089
3559586541,2711,206 660   4,408
45861134241,2561,031 498 17 3,925
528336258707972 343 50 2,649
632729301290391711182905571,879
71,350 1,08213368756 1,479 8655,733
81,346 1,2211541309824111,802 8676,913
91,705 1,327791399278051,428 9997,409
101,189431,055129949056121,705 7056,437
11171 4493930809568638 602,764
121,098 1,2161091567856461,336 4235,769
131,127 94321970930541972 4675,269
14151 373135123617171325 1912,086
15120 1181,0751,0909182971,373 6325,623
1695371361,2851,1609602611,481 5245,939
171491161351,6181,2091,6851781,283 5016,874
1834 58636998694 624 3203,364
1965 1061,3061,145376 842 6574,497
20139 1091,3941,394189 693 8174,735
2182 981,3391,169142 369 4703,669
223425621534521 456   2,479
23635461,1091,2581,062 500   4,610
Grand Total12,83163313,55617,51316,60511,7468,48316,640728,555106,634

 

With these data, e.g. the user # 1799142 processed 72 units in total in February, and in average 24 per hour. That is shown correctly with the first measure in the next chart detail:

 

Avg_per_Hr_individual_#_1799142.pngAverage volumen per hour for user # 1799142

And the overall average volumen per hour for the team is 4,443.08 (I verified it with a pivot table in the original excel file shown below):

 

Avg_per_Hr_data_overall.pngOverall average volume per hour in the original excel file

Could anyone please help?

Thanks,

Fernando

 

P.S. I don't know how to enclose my data

3 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

Re: Measure per hour per person and overall team

@calerof This should work...

 

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Hoja de Trabajo'[CONTAINERS_PROCESSED] ),
        ALL ( 'Hoja de Trabajo' )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Hoja de Trabajo'[Hora] ),
        ALL ( 'Hoja de Trabajo' )
    ),
    0
)

 

EDIT: Seems to work...

 

Measure per Hour.png 

calerof Regular Visitor
Regular Visitor

Re: Measure per hour per person and overall team

Hello Sean,

 

Thank you very much for your response. I enter the code exactly as you suggested:

 

Cont por Hora Equipo = DIVIDE(
				CALCULATE(
					SUM('Hoja de Trabajo'[CONTAINERS_PROCESSED]),
					ALL('Hoja de Trabajo')
				),
				CALCULATE(
					DISTINCTCOUNT('Hoja de Trabajo'[Hora]),
					ALL('Hoja de Trabajo')
				),
			0)

But my result is 7,823.79 instead of 4,443.08 as you show and what is the right amount. Weird, isn't it? I'm not sure what did I do wrong?

 

Avg_per_Hr_overall_different.pngResult of your suggested formula

Highlighted
calerof Regular Visitor
Regular Visitor

Re: Measure per hour per person and overall team

 

@Sean,

 

I just made a small change, i.e. ALLSELECTED as follows, and now it's up and working!

Thanks a lot!

Fernando

Cont por Hora Equipo = DIVIDE(
			CALCULATE(
			  SUM('Hoja de Trabajo'[CONTAINERS_PROCESSED]),
			  ALLSELECTED('Hoja de Trabajo')
			  ),
			CALCULATE(
			  DISTINCTCOUNT('Hoja de Trabajo'[Hora]),
			  ALL('Hoja de Trabajo')
			),
		      0)

 

Avg_per_Hr_overall_right.pngRight overall average volume per hour

4 REPLIES 4
Super User
Super User

Re: Measure per hour per person and overall team

@calerof This should work...

 

Measure =
DIVIDE (
    CALCULATE (
        SUM ( 'Hoja de Trabajo'[CONTAINERS_PROCESSED] ),
        ALL ( 'Hoja de Trabajo' )
    ),
    CALCULATE (
        DISTINCTCOUNT ( 'Hoja de Trabajo'[Hora] ),
        ALL ( 'Hoja de Trabajo' )
    ),
    0
)

 

EDIT: Seems to work...

 

Measure per Hour.png 

calerof Regular Visitor
Regular Visitor

Re: Measure per hour per person and overall team

Hello Sean,

 

Thank you very much for your response. I enter the code exactly as you suggested:

 

Cont por Hora Equipo = DIVIDE(
				CALCULATE(
					SUM('Hoja de Trabajo'[CONTAINERS_PROCESSED]),
					ALL('Hoja de Trabajo')
				),
				CALCULATE(
					DISTINCTCOUNT('Hoja de Trabajo'[Hora]),
					ALL('Hoja de Trabajo')
				),
			0)

But my result is 7,823.79 instead of 4,443.08 as you show and what is the right amount. Weird, isn't it? I'm not sure what did I do wrong?

 

Avg_per_Hr_overall_different.pngResult of your suggested formula

Highlighted
calerof Regular Visitor
Regular Visitor

Re: Measure per hour per person and overall team

 

@Sean,

 

I just made a small change, i.e. ALLSELECTED as follows, and now it's up and working!

Thanks a lot!

Fernando

Cont por Hora Equipo = DIVIDE(
			CALCULATE(
			  SUM('Hoja de Trabajo'[CONTAINERS_PROCESSED]),
			  ALLSELECTED('Hoja de Trabajo')
			  ),
			CALCULATE(
			  DISTINCTCOUNT('Hoja de Trabajo'[Hora]),
			  ALL('Hoja de Trabajo')
			),
		      0)

 

Avg_per_Hr_overall_right.pngRight overall average volume per hour

Super User
Super User

Re: Measure per hour per person and overall team

Ok great I was just going to suggest you do that!