cancel
Showing results for
Did you mean:
Highlighted Creator II

## Calculate the attendance based on a monthly basis in a Pivot Table

Hi All,

I need to calculate the percentage of attendees for each month by the total number of participants for the month.

(Total number of participants comprise of both "Attended" people and the "Did Not Attend" people).

Please refer the provided screenshot and the data set herewith.

For example, the respective percentage under month "December" for the Age Group "Age 0-19" should be 42.5% which is  17 / 17+5+15+3 (Attended number for the age group divided by the total number of participants for the selected month, which is "December".

Similarly, Percentage for the month "January" for the same group (Age 0-19) should be 21% ( 12/56).

I tried an expression as "=Num(Sum(Attended) / Sum(TOTAL <[Month]>Total_Attendance),'#,##0%')" to calculate the percentage (as per the calculated screen shot data) of attendance but does not give me the expected outcome.

Could someone please help me to derive the correct expression to calculate the correct percentage based on a monthly basis.

Kind regards,

Andy

Labels (2)

• ### pivot table

1 Solution

Accepted Solutions
Highlighted MVP

Try this

`=Num(Sum(Attended) / Sum(TOTAL <[Month], [Company]> Total_Attendance), '#,##0%')`

or

`=Num(Sum(Attended) / Sum(Aggr(NODISTINCT Sum(Total_Attendance), Month, Company)), '#,##0%')`
4 Replies
Highlighted Creator II

Hi All,

I was able to develop an expression to perform the above task, but it does the job partially.

The expression I developed is, "=Num(Sum(Attended) / Sum(Aggr(Sum(Total_Attendance),Month,Company)),'#,##0%')".

The above expression calculates the percentage value only for the first Age group range of each month but not for the rest.

When analysed bit deeper, found that the "Total Participants" were calculated only for the first age range of each month but not for the remainder.

Please see the attached screenshot herewith.

Appreciate a lot if someone could help me to resolve the said issue.

Kind regards,

Andy

Highlighted Creator II
Just a small not,
I have created a dimension during the data load process named, "Total_Attendance" which adds both "Attended" people and the "Did Not Attend" people.
Thank you.
Regards,
Andy
Highlighted MVP

Try this

`=Num(Sum(Attended) / Sum(TOTAL <[Month], [Company]> Total_Attendance), '#,##0%')`

or

`=Num(Sum(Attended) / Sum(Aggr(NODISTINCT Sum(Total_Attendance), Month, Company)), '#,##0%')`
Highlighted Creator II
Hi MVP,
You are a legend.  