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

     

     

v-kelly-msft's avatar
v-kelly-msft
Community Support
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