Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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,
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
112 | |
99 | |
73 | |
72 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |