Forum Discussion

milkmoneymike's avatar
milkmoneymike
Helper I
6 years ago
Solved

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 memb...
Need Help
  • v-kelly-msft's avatar
    v-kelly-msft
    6 years ago

    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's avatar
Anonymous
Not applicable
6 years ago

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?