countrow

[milzha]

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%

[AlexChen · Accepted 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

[GilesWalker]

@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