# Microsoft Office Application Help - Excel Help forum > Excel Charting & Pivots >  >  Pivot table to get percentage of attendance by department

## majana54

I need to create a pivot table for attendance of trainings. there will be several trainings that will be attended by all departments. I need to get the percentage of attendance as the months passed. is there a way to do it via pivot table? the percentage of attendance will be updated automatically as the master data sheet is updated. thank you in advance. PS. the pivot table will be used for a chart presentation

----------


## JeteMc

Perhaps this will help.
1. Use Get & Transform (aka. Power Query) to produce a data set of records using the following advanced editor code: 


```
Please Login or Register  to view this content.
```


Note that the code was used to produce a connection and the table is in the data model only.
2. Use Power Pivot to include the following measures:
fx Not attended:=COUNTBLANK(MasterT[Date])
fx Attended: =COUNT(MasterT[Date])
fx Percent:=[Attended]/([Attended]+[Not attended])
3. Produce a pivot table with Master cost center in the Rows area; Training in the Columns area; Percent in the Values area and the Month Name field in a slicer
Note that (blank) will always need to be selected in the slicer in order for the pivot table to correctly show the percent.
Let us know if you have any questions.

----------

