Forum Discussion

sohailstsindia's avatar
sohailstsindia
Icon for Helper I rankHelper I
8 years ago
Solved

CALULATE COUNT AND PERCENTAGE

EMPDATEPROJ
A1/1/2018P295
A1/2/2018P295
A1/3/2018P295
A1/4/2018P295
A1/5/2018P296
A1/6/2018P296
A1/7/2018P295
B1/1/2018P296
B1/2/2018P296
B1/3/2018P296
B1/4/2018P296
B1/5/2018P295
B1/6/2018P296
C1/1/2018P297
C1/2/2018P295
C1/3/2018 
C1/4/2018 
C1/5/2018P297
C1/6/2018P297
C1/7/2018P297

 

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.

 

EMPPROJCOUNTPERCENTAGE
AP295571.43
AP296228.57
BP295116.67
BP295583.33
C 228.57
CP295114.29
CP297457.14
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

     sohailstsindia

     

    May be a MEASURE like

     

    Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] ) )
  • v-xjiin-msft's avatar
    v-xjiin-msft
    8 years ago

    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.

4 Replies

  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    Icon for Community Champion rankCommunity Champion
    8 years ago

     sohailstsindia

     

    May be a MEASURE like

     

    Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] ) )
    • sohailstsindia's avatar
      sohailstsindia
      Icon for Helper I rankHelper I
      8 years ago
      CALCULATE ( COUNT ( TableName[DATE] ), ALLEXCEPT ( TableName, TableName[EMP] )

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

      • Zubair_Muhammad's avatar
        Zubair_Muhammad
        Icon for Community Champion rankCommunity Champion
        8 years ago

        sohailstsindia

         

        Ok. Try this one

         

        Percentage =
COUNT ( TableName[DATE] )
    / CALCULATE (
        COUNT ( TableName[DATE] ),
        ALLEXCEPT ( TableName, TableName[EMP], TableName[DATE].[Month] )
    )
  • v-xjiin-msft's avatar
    v-xjiin-msft
    Icon for Solution Sage rankSolution Sage
    8 years ago

    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.