Last 6 month weighted average total is inaccurate

[Avivek]

I tried to create a measure of last 6 month weighted average days, the measure is as follows;

Wt avg 2 = CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]*RECEIVABLES[Weights]))

 

Last 6 months Wt Avg4 =

VAR enddate = EOMONTH('LAST REFRESHED'[Data Last Refreshed],-1)

VAR startdate = EOMONTH(enddate,-6)+1

return

CALCULATE( [Wt avg 2],

FILTER('RECEIVABLES','RECEIVABLES'[Payment Recieved date]>= startdate && RECEIVABLES[Payment Recieved date]<= enddate))

If I check them individually, the 6 month weighted average shows correct value, showing me the last 6 month weighted average days as well as every month's weighted average days is also correct. But the total for the 6 month is incorrect and also does not match with the invidual values for the last 6 months.

[Anonymous]

You have to show the model. No way around it.

[MattAllington · Accepted solution]

You haven't provided enough information for me to give you an exact answer, but these issues are always of the category I call "totals don't add up". You can read more about that here https://exceleratorbi.com.au/use-sum-vs-sumx/

Your visual (rows inside) is doing the calc for individual "month & year", but your total does not do that. The total is for all months and years (which is not the same as doing it by month and year, then averaging them).  It's not clear to me if you need the SUMX or not, nor which table the "month & year" column comes from. But you will need some way of averaging the results by month at the total. A guess is it will be something like this (one option)

Last 6 months Wt Avg4 =
VAR enddate =
    EOMONTH ( 'LAST REFRESHED'[Data Last Refreshed], -1 )
VAR startdate =
    EOMONTH ( enddate, -6 ) + 1
RETURN
    CALCULATE (
        Averagex(values(table[month & year]),[Wt avg 2]),
        FILTER (
            'RECEIVABLES',
            'RECEIVABLES'[Payment Recieved date] >= startdate
                && RECEIVABLES[Payment Recieved date] <= enddate
        )
    )

It is likely there is a more efficient way to do it, but you would need to share the entire data model for that to be clear.