Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
Solved! Go to Solution.
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 to be a Super User!
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
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
Please provide the sample of your raw data and the result that you expected
Proud to be a Super User!
I hope that helps.
I want a CARD that shows the average meetings I have 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 to be a Super User!
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?
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
94 | |
81 | |
66 | |
58 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |