Forum Discussion
Help Creating Attendance sheet
Hello All
I am new to Power BI and would like to see the best way to track meeting attendance within our Board Members. I would like to show in a visual the percentage of meetings our Board members are attending. Im not entirely sure how to show that i can show the meetings each member went to but when i try and show the percentage of the grand total the percentages seem off. As you can see Skip has attended 100% of meetings and i would like to show that within a visual. 1's are marked as attended and the zero represents the User did not attend. Thanks in advanced for any help!
Here is the table i am working off of
| Name | R&R Q1 | Fall Board Meeting | Monthly Meeting Aug | Montly Meeting Sept | Monthly Meeting Dec | R&R Q2 |
| Keith | 1 | 1 | 1 | 1 | 0 | 1 |
| Matt | 1 | 1 | 1 | 1 | 0 | 0 |
| Doni | 0 | 1 | 0 | 1 | 1 | 1 |
| Bill | 0 | 1 | 1 | 0 | 1 | 1 |
| Melissa | 1 | 0 | 1 | 1 | 0 | 0 |
| Norm | 0 | 1 | 1 | 1 | 1 | 1 |
| Skip | 1 | 1 | 1 | 1 | 1 | 1 |
| Bryan | 1 | 1 | 1 | 1 | 1 | 1 |
| Gordon | 1 | 1 | 1 | 1 | 1 | 0 |
Cheers
Mike
Hi milkmoneymike ，
First you need to unpivot your table,steps are as below:
Go to Home>edit queries>select the first column>unpivot other columns:
Then you will get a table as below：
At last, you need a measure as below:
Measure = VAR a = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Value] = 1 ) VAR b = CALCULATE ( DISTINCTCOUNT ( 'Table'[Meeting] ), ALL ( 'Table' ) ) RETURN a / b + 0And you will see:
Btw,if you wanna calculate the percentage of "not attended",you just need to modify as 'Table'[Value]=0,
For the related .pbix file,pls click here.
Best Regards,
Kelly
4 Replies
Hey milkmoneymike ,
I reshaped the wide data format to a long data format by unpivoting the meeting, after doing this my table looks like this (just a fraction):
Then I created a measure that counts the number of meetings like so:
No Of All Meetings = CALCULATE( DISTINCTCOUNT('Table'[Meeting]) , ALL('Table') )For similar visuallizations tasks my favorite visual is the Bullet Chart by OKViz (you can add the visual for free from the marketplace). This is how it looks like:
The black horizontal line visualizes the number of meetings, the bar is just the sum of the column value.
This is how I configured the visual (be aware that I put the option "States" to off:
Hopefully this provides some ideas how you can tackle your challenge.
Regards,
Tom
After unpivoting all columns (in Query Editor) except the name column, and renaming the new column from "Attribute" to "Meeting Name," I created 2 measures:
# of Meetings Attended = CALCULATE(SUM('Sheet1'[Value]),ALLEXCEPT('Sheet1',Sheet1[Name]))
Total Number of Meetings = CALCULATE(DISTINCTCOUNT(Sheet1[Meeting Name]),ALL(Sheet1))
and then a third measure to calculate meeting attendance percentage, using the previous 2 measures:Attendance Percentage =DIVIDE('Sheet1'[# of Meetings Attended],'Sheet1'[Total Number of Meetings])
and then pulled those fields in to a table or clustered column chart:
If this works for you, can you please mark it as the solution?Hi milkmoneymike ，
First you need to unpivot your table,steps are as below:
Go to Home>edit queries>select the first column>unpivot other columns:
Then you will get a table as below：
At last, you need a measure as below:
Measure = VAR a = CALCULATE ( COUNT ( 'Table'[Value] ), ALLEXCEPT ( 'Table', 'Table'[Name] ), 'Table'[Value] = 1 ) VAR b = CALCULATE ( DISTINCTCOUNT ( 'Table'[Meeting] ), ALL ( 'Table' ) ) RETURN a / b + 0And you will see:
Btw,if you wanna calculate the percentage of "not attended",you just need to modify as 'Table'[Value]=0,
For the related .pbix file,pls click here.
Best Regards,
Kelly
Start off by unpivoting all columns except the name column, and take it from there.
