DAX function for average value per count data/rows

[Anonymous]

Hi,

I want to make a line chart for the value of the average [Decision Time (Days)] per count of [Notification] for each month.

The table is "Data". The fields are:

[Notification] (type: string)

[Date] (type: date)

[Decision Time (Days)] (type: number)

The formula will be something like:

AVGTIME = SUM([Decision Time (Days)]/COUNTA([Notification])

But it should be based on the counting per month.

Below is the sample data:

Notification	Date	Decision Time (Days)
AAAA	04/01/2020	15
BBBB	02/04/2020	11
CCCC	18/06/2020	7
DDDD	01/01/2020	27
EEEE	05/12/2020	5
FFFF	06/01/2020	24
GGGG	07/08/2020	15
HHHH	08/08/2020	16
IIII	09/08/2020	2
JJJJ	10/08/2020	10
KKKK	11/08/2020	26
LLLL	12/05/2020	12
MMMM	13/11/2020	19
NNNN	15/03/2020	23

Thank you for your help.
This will be a live data so I hope I can get a formulation where I don't always need to update the field of the formula.

Regards,

Vano

[lbendlin · Accepted solution]

your formula is already correct. All you need is to add a month column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdBLDsMgDEXRvTCOZGMCIcM2/f82gNj/NvpsRIp6Z+jEDkkp7oDc5Hgm9iQsjIOPrk7FHZGSELSTN9qQnjJx6rSYnJAO+WGfNDojpUheOrU3XZBKGodmoytSWojz3/1uSCmPlIzuSGkdSEweSJ/jcYiNnsg+cRxq+15ICb8i7lNt4RvpKZD//cDV6IPsusRhXxhcrV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Notification = _t, Date = _t, #"Decision Time (Days)" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From([Date],"en-GB")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Decision Time (Days)", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Date.Year([Custom])*100+Date.Month([Custom]))
in
    #"Added Custom1"

[v-alq-msft · Accepted solution]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Table:

You may create a calculated column and a measure as below.

Calculated column:

YearMonth = YEAR([Date])*100+MONTH([Date])

Measure:

Result = 
DIVIDE(
   SUM('Table'[Decision Time (Days)]),
   DISTINCTCOUNT('Table'[Notification]),
   0
)

Result:

Best Regards

Allan

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

[lbendlin · Accepted solution]

your formula is already correct. All you need is to add a month column.

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("XdBLDsMgDEXRvTCOZGMCIcM2/f82gNj/NvpsRIp6Z+jEDkkp7oDc5Hgm9iQsjIOPrk7FHZGSELSTN9qQnjJx6rSYnJAO+WGfNDojpUheOrU3XZBKGodmoytSWojz3/1uSCmPlIzuSGkdSEweSJ/jcYiNnsg+cRxq+15ICb8i7lNt4RvpKZD//cDV6IPsusRhXxhcrV8=", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Notification = _t, Date = _t, #"Decision Time (Days)" = _t]),
    #"Added Custom" = Table.AddColumn(Source, "Custom", each Date.From([Date],"en-GB")),
    #"Changed Type" = Table.TransformColumnTypes(#"Added Custom",{{"Decision Time (Days)", type number}}),
    #"Added Custom1" = Table.AddColumn(#"Changed Type", "Custom.1", each Date.Year([Custom])*100+Date.Month([Custom]))
in
    #"Added Custom1"