Calculated column for weighted average does not work for few rows

[Avivek]

I had created a calculated column for total weight calculation which was (days difference* payment recieved). This calculated column works fine for few rows fetching the correct value on multiplication but few rows the multiplication values do not match. let me share the screenshot for it:

The calculated column is ,

Weight = RECEIVABLES[Days Diff]*RECEIVABLES[PAYMENT_AMOUNT]

Since it did not fetch the value I needed I created a new measure

Weight(measure)= CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]*VALUE(sum(RECEIVABLES[PAYMENT_AMOUNT]))))

Though the measure works well but then the issue is that the total is not correct.

I need the total weight for another measure to fetch  me the weighted average days.

[v-yiruan-msft]

Hi @Avivek ,

You can refer the content in the following links to modify or create measure for getting the correct total values:

Power BI: Totals Incorrect

Why Your Total Is Incorrect In Power BI - The Key DAX Concept To Understand

Measure wrong total in Matrix Visual

If the above methods can't resolve your problem, please provide some sample data which applied on the visual and existed relationship if the data from multiple tables.

Best Regard

Rena

[Fowmy]

@Avivek 

Try:

Weight(measure)= CALCULATE(SUMX(RECEIVABLES,RECEIVABLES[Days Diff]* RECEIVABLES[PAYMENT_AMOUNT]))

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn 

[Avivek]

Thankyou @Fowmy , but this measure does't work, it gives the same issue like the calculated column

[Fowmy]

@Avivek 


The measure works for me, please check your model and the data.

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Avivek]

@Fowmy ,

 I tried the same measure but would you know why it is not working for me in the model.

[Fowmy]

@Avivek 

I am not sure how you have adapted the measure. The Weighted Measure (New) is calculating and dividing it by 2 I believe. 

________________________

Did I answer your question? Mark this post as a solution, this will help others!.

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube, LinkedIn

[Avivek]

@Fowmy 

Yes right, thats the thing. But I do not know why it does so because all the columns from the same table. Then how does it effect few data's and not the rest.

[Fowmy]

@Avivek 

It's hard to make a conclusion unless having a look at your PBIX file, you may share the file removing any confidential information to check.

Thanks

Fowmy

[Avivek]

Thankyou so much @Fowmy  but the report connects to live dataset, so its not possible to remove the confidential information alone. But I really appreciate your responses.