Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Counting Attendance Percentage per Function

Hi all,

 

My PBI is linked to a Sharepoint which is populated by a Power Apps. 

 

The PowerApp is updated once a day, and is meant for Tier reporting. During the submission of the PowerApp, the user selects a field with multiple checkbox to select which departments/functions are present for the meeting that day. This then translates to a Option column in Sharepoint with "A,B,C,D,E".


I converted this in PBI to a Matrix, and there is a separate query with 2 columns. 1 with the unique ID that corresponds to each meeting, and the other reflects the departments present for that meeting. 

Eg. (ABC present for meeting 1, AB present for meeting 2)

ID     Department
1       A
1       B
1       C
2       A

2       B

I want to create a query which trends the attendance of each department. I managed to come up with a measure that counts the number of meetings attended per function: 

  COUNTAx(filter(Attendance,[ID]),Attendance[field_1])
 
However, I am unable to create a static grand total of number of meetings held. I am using 
CALCULATE ( COUNTROWS(Tier2Data ), ALL ( Tier2Data ), however it is being affected by the filter/slicer. It thus results in a result of 100% as the 2 measures end up with the same value. 

 



The percentages should also not add up to 100%, I would expect there to be multiple functions that are attending the meetings 7/10 times (70%).

Can anyone help me with coming up with a measure that will achieve the above function?
General Comment
Need Help
  • jgeddes's avatar
    jgeddes
    3 years ago

    I agree with what the video told you. If I understand then, your attendence table looks like this

    If that is the case then you can take the distinct count of the meeting id to get the number of meetings. Which is what this formula does: calculate(DISTINCTCOUNT(attendanceTable[ID]),All(attendanceTable))

    I am thinking the error is occuring when you try to do a distinct count from the other table.

     

10 Replies

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    You can try;

    % Meetings Attended =
    var _allMeetings =
    calculate(DISTINCTCOUNT(attendanceTable[ID]),All(attendanceTable))
    Return
    DIVIDE([Meetings Attended],_allMeetings,0)
    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

       

      Unfortunately I get the same result as follows:

       

  • jgeddes's avatar
    jgeddes
    Super User
    3 years ago

    Do you have an attendance table and a Tier 2Data table or is it just one table you are working with?

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Hi,

       

      To clarify, I have 2 tables.

       

      So the original field I get from PowerApps concantenates all the different departments together. I watched a video and was told that it would be best to split out that column and create a new table which is illustrated in the images in the original post.

      • jgeddes's avatar
        jgeddes
        Super User
        3 years ago

        I agree with what the video told you. If I understand then, your attendence table looks like this

        If that is the case then you can take the distinct count of the meeting id to get the number of meetings. Which is what this formula does: calculate(DISTINCTCOUNT(attendanceTable[ID]),All(attendanceTable))

        I am thinking the error is occuring when you try to do a distinct count from the other table.

         

  • Shiv22's avatar
    Shiv22
    New Member
    1 year ago

    To calculate the attendance percentage for a specific function or event, use this simple formula:

    Attendance Percentage = (Number of People Attended ÷ Total Invited People) × 100

    Example:

    • If 80 people attended a function out of 100 invited:
      Attendance Percentage = (80 ÷ 100) × 100 = 80%

    This formula helps measure participation effectively.

    You can also use the online Attendance Percentage Calculator to save time.