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.

[Icey · Accepted solution]

Hi @Avivek ,

The calculated logic of total value of [Last 6 months Wt Avg4] measure works like what I explained in this post. Please refer.

You can try the measure as follows. Make sure replace Table with the actual name of the table.

New measure = SUMX(Table,[Last 6 months Wt Avg4])

This may give the same values in the rows, but the correct sum in total.

Best regards

Icey

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

[dedelman_clng]

Hi @Avivek  - 

Power BI tables/matrixes don't "sum" the way Excel does - they evaluate the measure in the context of the "total" for that visual, which often gives unexpected results, especially when dates are otherwise involved in the table.

What do you expect to see where you have circled the value?

David