Forum Discussion

vinaydavid's avatar
vinaydavid
Helper III
6 years ago
Solved

Count based on Percentage Category

Hi,   How should be go about to get the count of employees(distinct) in the below scenario.   Activity Emp Budget hrs hours a 1 2.5 2 b 1 2.5 2 c 1 2.5 2 x 2 3 1 ...
Need Help
  • v-lid-msft's avatar
    v-lid-msft
    6 years ago

    Hi vinaydavid ,

     

    First of all, we need to create a calculated table as x-axis, you can change the value in this table to generate different category:

     

    x-axis = 
ADDCOLUMNS (
    DATATABLE (
        "MinPercent", DOUBLE,
        "MaxPercent", DOUBLE,
        {
            { 0, 0.2 },
            { 0.21, 0.5 },
            { 0.51, 0.75 },
            { 0.76, 1 }
        }
    ),
    "Category", FORMAT ( [MinPercent], "0%" ) & " - "
        & FORMAT ( [MaxPercent], "0%" )
)

     

     

     Then we can create a measure as the value of this chart:

     

    EmplyCount = 
COUNTX (
    FILTER (
        SUMMARIZE (
            'Table',
            'Table'[Emp],
            "Percent", DIVIDE ( SUM ( 'Table'[hours] ), SUM ( 'Table'[Budget hrs] ), 0 )
        ),
        [Percent] > MAX ( 'x-axis'[MinPercent] )
            && [Percent] < MIN ( 'x-axis'[MaxPercent] )
    ),
    [Emp]
)

     

     


    Best regards,

     

sananthv's avatar
sananthv
Helper II
6 years ago

The first part is easy. If you want a count distinct and lets say your table is called Employees, we can create a measure as bellow.

 

DistinctEmp = DISTINCTCOUNT(Employees[Emp])

 

I did not understand the second part of the question

  • vinaydavid's avatar
    vinaydavid
    Helper III
    6 years ago

    Thanks for your reply,

     

    2nd part,

    Is to find the Productivity % of each employee (Hours/Budgeted hours - which I can do) and based on the % values, I need to categorize and show the count of employees falling in say, 0% - 20%, 21% - 50%, 51% - 75% & 76% - 100% in a bar chart.

     

    Thanks!

    • v-lid-msft's avatar
      v-lid-msft
      Community Support
      6 years ago

      Hi vinaydavid ,

       

      How about the result after you follow the suggestions mentioned in my original post?Could you please provide more details about it If it doesn't meet your requirement?


      Best regards,