cancel
Showing results for
Did you mean:  Helper III

## 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 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

Regards,

David

1 ACCEPTED SOLUTION  Community Support

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 =
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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4  Community Support

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 =
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,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  Helper II

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  Helper III

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!  Community Support

Hi @vinaydavid ,

Best regards,

Community Support Team _ Dong Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.  