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

[swaggerboy69]

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!

[Icey]

Hi @swaggerboy69 ,

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

[amitchandak]

@swaggerboy69 ,

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

refer :https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-La...

[ryan_mayu]

@swaggerboy69 

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

[swaggerboy69]

I hope that helps.

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

[ryan_mayu · Accepted solution]

@swaggerboy69 

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

[swaggerboy69]

Hello Ryan!

I know this is already solved and this works but do you know how to round off to it's nearest whole number?

[Anonymous]

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