Help Creating Attendance sheet

[milkmoneymike]

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

[v-kelly-msft · Accepted 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 + 0

And 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

[Anonymous]

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?

[TomMartens]

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

[PaulDBrown]

@milkmoneymike 

Start off by unpivoting all columns except the name column, and take it from there.