CALULATE COUNT AND PERCENTAGE

[sohailstsindia]

EMP	DATE	PROJ
A	1/1/2018	P295
A	1/2/2018	P295
A	1/3/2018	P295
A	1/4/2018	P295
A	1/5/2018	P296
A	1/6/2018	P296
A	1/7/2018	P295
B	1/1/2018	P296
B	1/2/2018	P296
B	1/3/2018	P296
B	1/4/2018	P296
B	1/5/2018	P295
B	1/6/2018	P296
C	1/1/2018	P297
C	1/2/2018	P295
C	1/3/2018
C	1/4/2018
C	1/5/2018	P297
C	1/6/2018	P297
C	1/7/2018	P297

WE CHOOSE DATE BETWEEN FORM SLICER, FROM 1/1/2018 TO 1/7/2018. AFTER THAT WE CALCULATE THE WORKING DAYS BASED ON PROJECT AND ITS PERCENTAGE(NO OF DAYS/TOTAL DAYS*100) FOR PARTICULAR PROJECT WITH EMPLOYEE.

RESULT LIKE BELOW.

EMP	PROJ	COUNT	PERCENTAGE
A	P295	5	71.43
A	P296	2	28.57
B	P295	1	16.67
B	P295	5	83.33
C		2	28.57
C	P295	1	14.29
C	P297	4	57.14

[Zubair_Muhammad · Accepted solution]

 @sohailstsindia

May be a MEASURE like

Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] ) )

[v-xjiin-msft · Accepted solution]

Hi @sohailstsindia,

In your scenario, you are using a date slicer to filter your data. Right?

If so, to achieve your requirement, you can create several measures with expression like below:

Count days based on Emp and Proj:

Count Days =
CALCULATE (
    COUNT ( 'Table'[DATE] ),
    FILTER (
        ALLSELECTED ( 'Table' ),
        'Table'[EMP] = MAX ( 'Table'[EMP] )
            && 'Table'[PROJ] = MAX ( 'Table'[PROJ] )
    )
)

Count total days based on Emp.

Total Days =
CALCULATE (
    COUNT ( 'Table'[DATE] ),
    FILTER ( ALLSELECTED ( 'Table' ), 'Table'[EMP] = MAX ( 'Table'[EMP] ) )
)

Then the percentage will be:

Percentage = [Count Days]/[Total Days] 

For sure, you can combine above measures into one.

Thanks,
Xi Jin.

[Zubair_Muhammad · Accepted solution]

 @sohailstsindia

May be a MEASURE like

Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] ) )

[sohailstsindia]

CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] )

its count full  data, i need to calculate perticular month data

[Zubair_Muhammad]

@sohailstsindia

Ok. Try this one

Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE (
        COUNT ( TableName[DATE] ),
        ALLEXCEPT ( TableName, TableName[EMP], TableName[DATE].[Month] )
    )