cancel
Showing results for
Did you mean:
Highlighted
Helper I

How to create a card that shows the average meetings per week

Hi guys!

I need help on how to have a card that would show the average meetings per week I have.

I have a bunch of meetings per week and I wanted to create a card that would show the average meetings I have for the week.

Sample data below, Thanks!

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Resident Rockstar

Re: How to create a card that shows the average meetings per week

Not sure if the logic of calcuation is total meetings divided by week numbers?

May be you can try below measure.

``````avg meeting =
VAR startdate=min('meeting'[Actualstart])
VAR enddate=MAX(meeting[Actualstart])
VAR week =roundup(( enddate-startdate)/7,0)
return COUNTROWS(meeting)/week``````

Proud a to be a Datanaut!
Thanks and BR
Ryan
6 REPLIES 6
Highlighted
Resident Rockstar

Re: How to create a card that shows the average meetings per week

Please provide the sample of your raw data and the result that you expected

Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Helper I

Re: How to create a card that shows the average meetings per week

I hope that helps.

I want a CARD that shows the average meetings I have per week.

Highlighted
Super User IV

Re: How to create a card that shows the average meetings per week

averagex(summarize(Table, table[Week], "_1",sum(Table[meeting])),[_1])

You need to create a date without  timestamp

Date = [actual start].date

And join it with date table and have week there

Then try like

averagex(summarize(Table, Date[Week], "_1",sum(Table[meeting])),[_1])

or

averagex(summarize(Date, Date[Week], "_1",sum(Table[meeting])),[_1])

For week related columns

My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Resolver I

Re: How to create a card that shows the average meetings per week

Also, your data's date/time column will need to be duplicated and change datatype to Date.
Rename new column to DateID

You will need a Date Table to be able to join to your DateID column and hold the week start dates.

Click Transform Data to open PowerQuery Editor

Create two Parameters to seed a Date Table:
StartYear (Decimal Number) Value: 2020
EndYear (Decimal Number) Value: 2021

Create the Date Table: New Source, Blank Query, rename the table DateDim
On DateDim table, open Advanced Editor and copy and paste this code:

let
StartDate = #date(StartYear,1,1),
EndDate = #date(EndYear,12,31),
NumberOfDays = Duration.Days( EndDate - StartDate ),
Dates = List.Dates(StartDate, NumberOfDays+1, #duration(1,0,0,0)),
#"Converted to Table" = Table.FromList(Dates, Splitter.SplitByNothing(), null, null, ExtraValues.Error),
#"Renamed Columns" = Table.RenameColumns(#"Converted to Table",{{"Column1", "DateID"}}),
#"Changed Type" = Table.TransformColumnTypes(#"Renamed Columns",{{"DateID", type date}}),
#"Inserted Year" = Table.AddColumn(#"Changed Type", "Year", each Date.Year([DateID]), type number),
#"Inserted Month" = Table.AddColumn(#"Inserted Year", "Month", each Date.Month([DateID]), type number),
#"Inserted Month Name" = Table.AddColumn(#"Inserted Month", "Month Name", each Date.MonthName([DateID]), type text),
#"Inserted Quarter" = Table.AddColumn(#"Inserted Month Name", "Quarter", each Date.QuarterOfYear([DateID]), type number),
#"Inserted Week of Year" = Table.AddColumn(#"Inserted Quarter", "Week of Year", each Date.WeekOfYear([DateID]), type number),
#"Inserted Week of Month" = Table.AddColumn(#"Inserted Week of Year", "Week of Month", each Date.WeekOfMonth([DateID]), type number),
#"Inserted Day" = Table.AddColumn(#"Inserted Week of Month", "Day", each Date.Day([DateID]), type number),
#"Inserted Day of Week" = Table.AddColumn(#"Inserted Day", "Day of Week", each Date.DayOfWeek([DateID]), type number),
#"Inserted Day of Year" = Table.AddColumn(#"Inserted Day of Week", "Day of Year", each Date.DayOfYear([DateID]), type number),
#"Inserted Day Name" = Table.AddColumn(#"Inserted Day of Year", "Day Name", each Date.DayOfWeekName([DateID]), type text),
#"Inserted Start of Week" = Table.AddColumn(#"Inserted Day Name", "Start of Week", each Date.StartOfWeek([DateID]), type date)
in
#"Inserted Start of Week"

Now you have a DateDim table with DateID as the key

Close and apply to close Power Query Editor

Click the Model icon in the left NAV to open the model pane. Join the two tables with click and drag DateID to DateID

Create these three measures (my data table is Query1)
_CountRows = COUNTROWS(Query1)
_CountWeeks = DISTINCTCOUNT(DateDim[Start of Week])
_AverageMeasure = DIVIDE([_CountRows],[_CountWeeks])

Three cards with date slicer are in the pic.

HTH,

Smitty

Highlighted
Resident Rockstar

Re: How to create a card that shows the average meetings per week

Not sure if the logic of calcuation is total meetings divided by week numbers?

May be you can try below measure.

``````avg meeting =
VAR startdate=min('meeting'[Actualstart])
VAR enddate=MAX(meeting[Actualstart])
VAR week =roundup(( enddate-startdate)/7,0)
return COUNTROWS(meeting)/week``````

Proud a to be a Datanaut!
Thanks and BR
Ryan
Highlighted
Community Support

Re: How to create a card that shows the average meetings per week

Is this problem solved?

If it is solved, please always accept the replies making sense as solution to your question so that people who may have the same question can get the solution directly.

If not, please let me know.

Best Regards,
Icey

Announcements

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors