Meeting Attendance Report

[kkieger]

Hi,

I’ve sifted through a handful of related search topics, but am missing the key thread. Attached are 1) a basic meeting attendance tracking sheet and 2) the associated pbix built from the Excel source.

My confusion lies in the use of Meeting dates as column headings which I have unpivoted in Power Query Editor. With an objective to show a visual like the matrix one I built (which needs the Attendee=1 filter), but with basic new measures for a) total meetings held (8 to date), b) % of meetings attended by the person (Name). As a secondary issue - again, easier in a table visual than a matrix - I’d like Name and Total to be one row rather than expanded.

I’m just not clear if unpivoting the Date column headings - and changing their Type=Date is a benefit or not. Can you take a stab at this table and its “count of meetings held” and “% attended” by each person or persons?
BTW, the Total counts from the matrix visual are a positive to show, and there is no need for another measure of “% of all invitees who attended a specific meeting [date]”.

Thank you,
Kevin

OG Liaison Attendance.xlsx (13.9 KB)
ITS Operating Group Laiasons - Meeting Attendance Report.pbix (51.0 KB)

[kkieger]

@kkieger

I’d like Name and Total

Sorry… I meant Name and Title.

[kkieger]

At its core, it’s likely a question over pivoting vs. unpivoting, and applying the appropriate DAX after Power Query Editor structures the data properly … hoping the community has an approach to try.

I’m confident that once this spreadsheet produces the requested report & measures that when columns are inserted for new meeting dates and attendees, I’ll understand how the recommended approach handles it.

Thanks,
Kevin

[BolajiO]

Hi Kevin.
Unpivoting is the best way to go like you have done. As that’s the only way you build out a proper data model.

Now, I couldn’t quite get what it is you wanted to solve but here are two things I think you wanted to solve.

1. Measure to calculate Meetings Attended (without needing the filter)
2. Visual to show the name and total on one row.

For the first, here’s a little modification to “Total Attended” measure

Total Attended = 
CALCULATE(
    COUNTROWS( Data ),
    Data[Attended] = 1
)

And here’s an image showing the second solution

The steps to achieve this.

1. Expand all the row headers
   

   

2. On the format pane, go to the row headers section, and under “Options” turn off Stepped Layout and +/- icons.
   
   

   

3. Turn off row totals and you should have this
   

   

Hope that helps.

Here is the .pbix file
ITS Operating Group Laiasons - Meeting Attendance Report.pbix (52.5 KB)

[kkieger]

Thank you!
Definitely an excellent advancement.
‘Attended 2’ is useful on a person-by-person basis.
I’ve attached a basic table visual to illustrate the remaining "Total Meetings Held’ and the “% Attended” measure.
‘Total Meetings Held’ should be the count of meeting dates… there are currently 8 in total.
‘% Attended’ should equate to DIVIDE(‘Attended 2’, ‘Total Meetings Held’).
Thus, ‘Total Meetings Held’ - as meeting dates (columns) continue to be added and unpivoted - is the key remaining measure.
note: I don’t believe "% Attended (2)’ is currently accurate here as the table visualization shows.

Your recommended approach?

Thank you,
Kevin

[BolajiO]

Alright.
Here’s how I would write the measures

Total Meetings Attended:

Attended (2) = 
CALCULATE(
    DISTINCTCOUNT( Data[Meeting Date] ),
    Data[Attended] = 1
)

Total Meetings Held:

Total Meetings Held = 
CALCULATE(
    DISTINCTCOUNT( Data[Meeting Date] ),
    REMOVEFILTERS( Data[Meeting Date] )
)

% Attended:

% Attended (2) = 
DIVIDE( [Attended (2)], [Total Meetings Held] )

With those measures, we get the following results.

Here is the pbix.
ITS Operating Group Laiasons - Meeting Attendance Report.pbix (57.0 KB)

[kkieger]

Awesome. Thank you.