Custom Column showing average based on multiple conditions

[shaunparsons66]

I have a PBI with a simple table (see below for a very simplified version).

I would like to create two additional custom columns:

Custom Column 1: 'Month' - Simply shows the first day in the month, based on the 'Date' column below. For example, '01/03/2022' for Site 2.

Custom Column 1: 'Month Avg Daily Visitors' - This calcualtes the total visitors across the entire month for the particular site, divided by the number of working days in that month. For example, if a Site has 1000 total visitors in the month of September, the average daily visitors would be 1000/22 = 45

Any help would be greatly appreciated!

Data Table:

Site	Date	Visitors
Site 1	01/01/2022	15
Site 2	15/03/2022	25
Site 3	11/08/2022	45

[v-rongtiep-msft]

Hi @shaunparsons66 ,

I have created a simple smaple, please refer to it to see if it helps you.

Create three custom columns.

Date.StartOfMonth([Date])

List.Sum(List.Transform(
{Number.From(Date.StartOfMonth([Date]))..Number.From(Date.EndOfMonth([Date]))},
each if  Date.DayOfWeek(Date.From(_),0) =0 or Date.DayOfWeek(Date.From(_),0) =6 then 0 else 1))

[Visitors]/[Custom.1]

If I have misunderstoo dyour menaing, please provide more details with your desired output.

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shaunparsons66]

Hello

Thank you for your reply. This is half of what I need (your solution of counting the number of working days in each month is very helpful). However what I need to do is sum 'Visitors' column for each month (for example, total in June, total in July, etc), then divide by the number of working days in the month.

So all I now need is a custom column that sums 'Visitors' for for each site, and for each month. So for example, if Site 1 has 1000 visitors in June, this new custom column should calculate 1000/22 (if the number of working days in June is 22) = 45. It would be this value for every Site 1 row within that month.

[v-rongtiep-msft · Accepted solution]

Hi @shaunparsons66 ,

Please refer to my steps.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WCs4sSVXSUXJJBFNhmcWZJflFxUqxOhApBUOgqJGBkZG+oT6IaWiKkDKCSRnrA4WBbCQ5Y5ichb4hSJ8JRK4YJGeCMBKkzdxAKTYWAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [#"(blank)" = _t, #"(blank).1" = _t, #"(blank).2" = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"(blank)", type text}, {"(blank).1", type text}, {"(blank).2", type text}}),
    #"Promoted Headers" = Table.PromoteHeaders(#"Changed Type", [PromoteAllScalars=true]),
    #"Changed Type1" = Table.TransformColumnTypes(#"Promoted Headers",{{"Site", type text}, {"Date", type date}, {"Visitors", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type1", "Custom", each Date.StartOfMonth([Date])),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "Custom.1", each List.Sum(List.Transform(
{Number.From(Date.StartOfMonth([Date]))..Number.From(Date.EndOfMonth([Date]))},
each if  Date.DayOfWeek(Date.From(_),0) =0 or Date.DayOfWeek(Date.From(_),0) =6 then 0 else 1))),
    #"Added Custom2" = Table.AddColumn(#"Added Custom1", "Custom.2", each [Visitors]/[Custom.1]),
    #"Added Custom3" = Table.AddColumn(#"Added Custom2", "year", each Date.Year([#"Date"])),
    #"Added Custom4" = Table.AddColumn(#"Added Custom3", "Custom.3", each Date.Month([#"Date"])),
    #"Renamed Columns" = Table.RenameColumns(#"Added Custom4",{{"Custom.3", "month"}}),
    #"Changed Type2" = Table.AddColumn(#"Renamed Columns", "res", each List.Sum(Table.SelectRows(#"Renamed Columns", (x)=>x[year]=[year] and x[month]=[month])[Visitors])),
    #"Added Custom5" = Table.AddColumn(#"Changed Type2", "Custom.3", each [res]/[Custom.1]),
    #"Renamed Columns1" = Table.RenameColumns(#"Added Custom5",{{"Custom.1", "working days"}}),
    #"Removed Columns" = Table.RemoveColumns(#"Renamed Columns1",{"Custom.2"}),
    #"Renamed Columns2" = Table.RenameColumns(#"Removed Columns",{{"res", "summonth value"}, {"Custom", "firstday"}})
in
    #"Renamed Columns2"

Best Regards

Community Support Team _ Polly

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[shaunparsons66]

Thank you - The reason I don't want to use measures is because I want to plot the values on a line char.t

Regarding your proposed Power Query formulas, I'm afraid I keep getting the following error message:

"Token Eof Expected"

It keeps highlighting the " = eo" section of the formula.

To be clear, I'm in Query Editor and selecting 'add Custom Column' and inputting the above into the 'Custom Column' window that appears

[amitchandak]

@shaunparsons66 , I think you should use measure for that. But still

new column 1 =

var _date = eomonth([Date],0)+1

return

calculate(Sum(Table[Visitor]), filter(Table, Table[Date] =_date))

Avg COlumn

new column 1 =

var _st= eomonth([Date],0)+1

var _end=eomonth([Date],0)

return

Divide(calculate(Sum(Table[Visitor]), filter(Table, eomonth([Date],0)=_end)), networkdays(_start,_end,1))