Worklog - Total is Wrong

[Anonymous]

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

[Anonymous · Accepted solution]

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

 

Reference link:
Clever Hierarchy Handling in DAX

Regards,

Xiaoxin Sheng

[Anonymous]

Thank you Xiaoxin, this is really helpful.