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.
I have two table as below. One shows who took what classes, and the other one shows who report to whom. I have troublems adding colum "Total employees report to" into the final view. Should be simply and i am using add new column and countrow function, but it's not working. Can someone help me with this?
Class:
Employee ID Class
A Cooking
B Math
C English
D Cooking
Report To:
Employee ID Reoport to
A Joe
B Mary
C Joe
D John
Final view:
Report to Count of employees take Cooking Class Total employees report to Percentage
Joe 1 2 50%
Mary 0 1 0%
John 1 1 100%
Solved! Go to Solution.
Hi,
You can create a table using code below to do this.
Table = SUMMARIZE(ReoportTo, ReoportTo[Reoportto],
" Count of employees take Cooking Class", if(COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class]) = BLANK(), 0, COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class])),
" Total employees report to", COUNTA(ReoportTo[EmployeeID]),
"percentage", ((if(COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class]) = BLANK(), 0, COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class])) / COUNTA(ReoportTo[EmployeeID])) * 100) & "%" )
This is the result:
Best Regards
Alex
Hi,
You can create a table using code below to do this.
Table = SUMMARIZE(ReoportTo, ReoportTo[Reoportto],
" Count of employees take Cooking Class", if(COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class]) = BLANK(), 0, COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class])),
" Total employees report to", COUNTA(ReoportTo[EmployeeID]),
"percentage", ((if(COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class]) = BLANK(), 0, COUNTAx(FILTER(Class, Class[Class] = "Cooking"), Class[Class])) / COUNTA(ReoportTo[EmployeeID])) * 100) & "%" )
This is the result:
Best Regards
Alex
@milzha Are you able to show an example of the tables and how they are connected.
For this to work I would suggest you use a dax measure not a calculated column. Create the following measures:
Class count = COUNTROWS(Class table)
Report to = COUNTROWS(Report to table)
These will calculate the rows you are requiring. Then it is just a matter of how you want the data displayed.
If this doesnt work let me know.
Giles
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |