cancel
Showing results for
Did you mean:
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:

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

Thanks,

Fernando

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

3 ACCEPTED SOLUTIONS

Accepted Solutions
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...

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?

Highlighted
Regular Visitor

## Re: Measure per hour per person and overall team

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

4 REPLIES 4
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...

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?

Highlighted
Regular Visitor

## Re: Measure per hour per person and overall team

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

Super User

## Re: Measure per hour per person and overall team

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