cancel
Showing results for
Did you mean:
Highlighted
Frequent Visitor

## Worklog - Total is Wrong

Hi,

I am working on the Team Worklog and for some reason the Capacity total is coming wrong.

It's a simple worklog. The total for Capacity should be '469' but it's showing '784'.

The Measure I am using to calculate Capacity is

Capacity = DISTINCTCOUNT(Worklogs[Full name]) * DISTINCTCOUNT(Worklogs[Work date]) * 7

Utilization = SUM(Worklogs[Hours])

% Utilization = Worklogs[Utilization] / Worklogs[Capacity]

I guess is there is a better way to calculate the Capacity team.

My log looks something like this.

 Full name Work date Hours Member 1 2018-2-5 00:00 2 Member 1 2018-2-5 00:00 2 Member 1 2018-2-5 00:00 2 Member 2 2018-2-5 00:00 2 Member 3 2018-3-14 00:00 1 Member 2 2018-3-14 00:00 2 Member 2 2018-3-14 00:00 2 Member 3 2018-2-8 00:00 3 Member 2 2018-2-5 00:00 0.25 Member 2 2018-2-7 00:00 0.5 Member 3 2018-2-9 00:00 0.25 Member 3 2018-2-13 00:00 0.25 Member 3 2018-2-14 00:00 0.25 Member 3 2018-3-6 00:00 2 Member 2 2018-3-1 00:00 1 Member 3 2018-2-19 00:00 2 Member 1 2018-2-20 00:00 3 Member 1 2018-2-21 00:00 0.5

Thanks,

Joydeep

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team

## Re: Worklog - Total is Wrong

Hi @joydeep7,

AFAIK, measure formulas has different calculation logic on total level. You need to add some conditions and specific formula to handling total level calculation.

Sample formula:

```Capacity =
IF (
ISFILTERED ( Worklogs[Full name] ),
DISTINCTCOUNT ( Worklogs[Full name] ) * DISTINCTCOUNT ( Worklogs[Work date] )
* 7,
SUMX (
SUMMARIZE (
Worklogs,
[Full name],
[Work date],
"Capa", DISTINCTCOUNT ( Worklogs[Full name] ) * DISTINCTCOUNT ( Worklogs[Work date] )
* 7
),
[Capa]
)
)```

Clever Hierarchy Handling in DAX

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
2 REPLIES 2
Community Support Team

## Re: Worklog - Total is Wrong

Hi @joydeep7,

AFAIK, measure formulas has different calculation logic on total level. You need to add some conditions and specific formula to handling total level calculation.

Sample formula:

```Capacity =
IF (
ISFILTERED ( Worklogs[Full name] ),
DISTINCTCOUNT ( Worklogs[Full name] ) * DISTINCTCOUNT ( Worklogs[Work date] )
* 7,
SUMX (
SUMMARIZE (
Worklogs,
[Full name],
[Work date],
"Capa", DISTINCTCOUNT ( Worklogs[Full name] ) * DISTINCTCOUNT ( Worklogs[Work date] )
* 7
),
[Capa]
)
)```

Clever Hierarchy Handling in DAX

Regards,

Xiaoxin Sheng

Community Support Team _ Xiaoxin Sheng
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

For learning resources/Release notes, please visit: | |
Frequent Visitor

## Re: Worklog - Total is Wrong

Thank you Xiaoxin, this is really helpful.