Forum Discussion

milzha's avatar
milzha
Icon for Microsoft Employee rankMicrosoft Employee
10 years ago
Solved

countrow

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...
  • AlexChen's avatar
    AlexChen
    10 years ago

    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's avatar
GilesWalker
Icon for Skilled Sharer rankSkilled Sharer
10 years ago

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