Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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,
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,
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
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!
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,
User | Count |
---|---|
125 | |
106 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |