Measure per hour per person and overall team

[calerof]

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:

Average 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:

Overall 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_month	2
Sum of CONTAINERS_PROCESSED	User #
Row Labels	U1306535	U1322541	U1326259	U1434227	U1434234	U1522978	U1704125	U1718016	U1799142	U1985457	Grand Total
0	695	63	845	1,191	1,151		682				4,627
1	440	87	660	1,029	1,043		628				3,887
2	143		258	327	253		108				1,089
3	559	58	654	1,271	1,206		660				4,408
4	586	113	424	1,256	1,031		498		17		3,925
5	283	36	258	707	972		343		50		2,649
6	327	29	301	290	391	71	118	290	5	57	1,879
7	1,350		1,082	133	68	756		1,479		865	5,733
8	1,346		1,221	154	130	982	411	1,802		867	6,913
9	1,705		1,327	79	139	927	805	1,428		999	7,409
10	1,189	43	1,055	129	94	905	612	1,705		705	6,437
11	171		449	39	30	809	568	638		60	2,764
12	1,098		1,216	109	156	785	646	1,336		423	5,769
13	1,127		943	219	70	930	541	972		467	5,269
14	151		373	135	123	617	171	325		191	2,086
15	120		118	1,075	1,090	918	297	1,373		632	5,623
16	95	37	136	1,285	1,160	960	261	1,481		524	5,939
17	149	116	135	1,618	1,209	1,685	178	1,283		501	6,874
18	34		58	636	998	694		624		320	3,364
19	65		106	1,306	1,145	376		842		657	4,497
20	139		109	1,394	1,394	189		693		817	4,735
21	82		98	1,339	1,169	142		369		470	3,669
22	342	5	621	534	521		456				2,479
23	635	46	1,109	1,258	1,062		500				4,610
Grand Total	12,831	633	13,556	17,513	16,605	11,746	8,483	16,640	72	8,555	106,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:

Average 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):

Overall 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

[Sean · Accepted solution]

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

 

[calerof · Accepted solution]

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?

Result of your suggested formula

[Sean · Accepted solution]

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

 

[calerof · Accepted solution]

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?

Result of your suggested formula

[calerof · Accepted solution]

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

Right overall average volume per hour

[Sean]

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