Count based on Percentage Category

[vinaydavid]

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
y	2	3	1

Then I want to plot the count on a clustered bar chart...like below

For 2 employees:

Category (y-axis) 50% - 80% ----------> 1

                            30% - 49%-----------> 1

Thanks in advance for your time and efforts.

Regards,

David

[v-lid-msft · Accepted solution]

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]

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]

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]

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,