Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello,
I have one question.
I'm using a Sharepoint list and in Power BI I'm counting the ideas by employees of company (employees are grouped by departament).
I have to calculate what percentage of employees in the department have provided ideas.
For example:
Departament have 86 employees (it's should be static number, I will enter this number by hand, because we don't have integration for this).
86 employees - 100%
56 ideas by employess - x%
Must calculate and display x value. X=65%
Hi @Anonymous , Could you show your raw data and your expected result?
Hi @ZunzunUOC ,
for example sharepoint list looks like that:
ID | Status | Departament | Name | Idea | Description |
1 | Completed | Finance | John | Create report | Text |
2 | Completed | Information technology | Peter | Automate reporting | Text |
3 | Completed | Finance | Martin | Improve the procurement process | Text |
For example, the Finance Department has 4 people, but in the list are just 2 ideas. How to calculate:
4 employees - 100%
2 ideas - x%
X=50%. In Power BI dashboard I need to show 50%.
I wrote earlier that I will write the number of employees in the department by hand, because it is not on the list.
Assume the following are the tables in your data model.
Table 1: EmployeeStrength
Department | Strength |
Finance | 4 |
Information technology | 6 |
Table 2: Ideas
ID | Status | Departament | Name | Idea | Description |
1 | Completed | Finance | John | Create report | Text |
2 | Completed | Information technology | Peter | Automate reporting | Text |
3 | Completed | Finance | Martin | Improve the procurement process | Text |
Relationship: EmployeeStrength[Department] --> Ideas[Department]
RelationShip Type: One to Many
The following measures will give you the required output.
DepartmentStrength = SUMX(EmployeeStrength,EmployeeStrength[Strength])
EmployeeCountOnIDeas = COUNTROWS(Ideas)
PercentageOfEmployeesSubmittedIdea = DIVIDE([EmployeeCountOnIDeas],[DepartmentStrength],0)
Format the last measure to percentage format.
User | Count |
---|---|
124 | |
106 | |
99 | |
63 | |
59 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |