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-xicai · Accepted solution]

Hi @Avivek ,

You may create a new measure like DAX below to get the correct total.

Weight(measure)_New=

var _table = SUMMARIZE(RECEIVABLES, RECEIVABLES[Month & Year],"_Value", [Weight(measure)] )

return
IF(HASONEVALUE(RECEIVABLES[Month & Year]), [Weight(measure)], SUMX(_table,[_Value]))

Best Regards,

Amy 

Community Support Team _ Amy

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

[Fowmy]

@Avivek 

Try this

Weight(measure)= 
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 

[Fowmy]

@Avivek 

Check this question for the solution:
https://community.powerbi.com/t5/Developer/Calculated-column-for-weighted-average-does-not-work-for-...

Thanks

Fowmy

[Avivek]

@Fowmy thankyou but it may be the image problem, the data does not have decimal, the format is same for the whole colum, PFA one such screenshot.

[Fowmy]

@Avivek

Check this post, you will find the issue with your data:
https://community.powerbi.com/t5/Developer/Calculated-column-for-weighted-average-does-not-work-for-...

________________________

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

[Fowmy]

@Avivek 


Is this post duplicated?

https://community.powerbi.com/t5/Developer/Calculated-column-for-weighted-average-does-not-work-for-...

________________________

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]

Yes @Fowmy 

[amitchandak]

@Avivek , it should be

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

Can you share sample data and sample output in table format?

[Avivek]

@amitchandak ,

I had tried the same measure but it gives me the same error as the calculated column

[amitchandak]

@Avivek , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

Multiplication should be done at row level and division at aggregated level

[Avivek]

@amitchandak , please find the link-

https://globusmedical-my.sharepoint.com/:u:/p/avivek/EV6JuAcYEPlDlQ9mqQmklBcBnDW1qmfDKIitaCebM8B1FA?...

But this file I have taken it from the excel after filtering the confidential part but i do not see the total issue in the measure here but I can see that issue in the report when it is connected to the model, let me share the screenshot with the same data from the report connected to the model

-