Measure for Average of Sum of Collections per month

[TwiggyHaz]

Hello,

Looking to recreate the below SQL into a DAX equivalent, currently using DirectQuery for the Dashboard being created.

SELECT		AccountID,
		AVG(Q1.Total) AS Average

FROM 
		(
			SELECT		AccountID,
					FORMAT(EntryDate,'yyyyMM') AS Period,
					SUM(Amount) AS Total

			FROM		Collections

			GROUP BY	AccountID,
					FORMAT(EntryDate,'yyyyMM')
		) Q1

GROUP BY	AccountID

I currently have the following in DAX, but the output is incorrect. Any ideas where I am going wrong?

Test = 
CALCULATE (
    AVERAGEX (
        SUMMARIZE (
            Collections,
            'Base Data'[AccountID],
            Dates[Period],
            "Collections", Sum ( Collections[Amount] )
        ),
        [Collections]
    ),
    USERELATIONSHIP ( Collections[EntryDate], Dates[DateID] ),
    Collections[PreStrat] = "Yes"
)

[v-jayw-msft]

Hi @ TwiggyHaz,

Please check following steps as below and see if the result achieve your expectation.

1. Create calculated table:

    Date = DISTINCT(Collections[EntryDate])

2. Create calculated column:

    YearMonth = FORMAT('Date'[EntryDate],"YYYYMM")

3. Create measure:

    Measure =

    VAR countYM =

        CALCULATE (

            DISTINCTCOUNT ( 'Date'[YearMonth] ),

           FILTER ( Collections, Collections[AccountID] = MAX ( Collections[AccountID] ) )

        )

    RETURN

        SUM ( Collections[Amount] ) / countYM

Result would be shown as below, hopefully works for you:

Best Regards,

Jay

Community Support Team _ Jay Wang

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