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

     

     

TomMartens's avatar
TomMartens
Super User
6 years ago

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