Measures do work if filters are removed

[Avivek]

I had created a measure for weighted avg which works fine when i check it with refernce to an account or the payment recieved date.

But the same thing when i try to see in a matrix without applying filter the value changes and I am not able to understand why is it so.

The above picture I have removed the date filter and for -6/2020 I have highlighted which shows a value of 498 which does not match with the above picture.

Below are the measures I have used for weighted average:

Weighted Avd days = CALCULATE(SUM(RECEIVABLES[Days Diff])*[Weights])

Weights = DIVIDE(SUM(RECEIVABLES[Weight]),[total weight])

[amitchandak]

@Avivek , Multiplication should always have row context

CALCULATE(SUM(RECEIVABLES[Days Diff])*[Weights],values(Table[Parent Account]))

or

sumx(values(Table[Parent Account]),SUM(RECEIVABLES[Days Diff])*[Weights])

use this

[Avivek]

@amitchandak , can I add anothher glitch which I face,the value which we can see in the matrix for that particular account forjune month is 498.09

But same thing if i try to add other columns and check it shows a total of 3324

And when exported in excel it shows a total of 178

Would you know why is that. appreciate your response in advance

[v-kelly-msft]

Hi  @Avivek ,

Could you pls upload your .pbix file to onedrive business and share the link with us?Remember to remove the confidential information.

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Avivek]

Sorry, since its a live dataset I can't share the model file and using the the excel to create a similar report does not show any errors, but let me share a scenario if it helps.

here you can see that the values do not match up with the overall total.

Weighted Avg days = CALCULATE(SUM(RECEIVABLES[Days Diff])*[Weights])

Weights = DIVIDE(SUM(RECEIVABLES[Weight]),[total weight])

Weight = RECEIVABLES[Days Diff]*RECEIVABLES[PAYMENT_AMOUNT]------this is a calculated column

Days Diff = (RECEIVABLES[Payment Recieved date])-(RECEIVABLES[Invoice Date])

I hope this helps, let me know if any other information needed.

[v-kelly-msft]

Hi  @Avivek ,

Try to use the measure below:

measure=sumx(values(RECEIVABLES[Invoice Date]),[Weighted Avg days])

 
Best Regards,
Kelly

Did I answer your question? Mark my post as a solution!

[Avivek]

Thank you @amitchandak , this was a great piece of information, did not know this. Appreciate your help.