Calculating Weighted Average

[Catman]

I would like to calculate the weighted average value for each customer like below:

I need to calculate the Total Amount by customer and the weight %, then I can multiply by the Payment Days to obtain the weighted average.

Working
Customer	Invoice	Total Amount by Customer	Amount/Total	Amount	Paymentdays	Average Payment Days by Customer
A	10001	900,000.00	10.2054%	91,848	90	9
A	10002	900,000.00	37.5477%	337,929	45	17
A	10003	900,000.00	4.9367%	44,430	90	4
A	10004	900,000.00	39.5216%	355,695	45	18
A	10005	900,000.00	7.7886%	70,097	90	7
				900,000		55

In the summary should be shown:

Customer	Average Payment Days	Total Amount
A	55	900,000

Any help is appreciated, thanks!

[Anonymous]

Hi @Catman ,
I hope the suggested solution worked for you. If your issue is resolved, kindly accept the helpful post as a solution — it helps the community identify helpful answers more easily.If still facing issues, feel free to reachout!
Thank you.

Regards,
Pallavi G.

[Anonymous]

Hi @Catman ,
Thank you community members for the helpful insights!
Following up to check whether you got a chance to review the suggestion given.If it helps,consider accepting the helpful answer as solution,it will be helpful for other members of the community who have similar problems as yours to solve it faster.
For precise help,could you please sample data and expected output.Glad to help.
Thank you.

Regards,
Pallavi.

[Ashish_Mathur]

Hi,

Which of those columns in Table1 are input columns?  HOw did you calculate the last column in Table1?

[ryan_mayu]

could you pls provide some sample data?

[Catman]

Hi, how can I upload an excel in this post? I have the calculation data in excel or do I need to open a new post? Sorry I am quite new to this fabric community. Thanks.

[ryan_mayu]

you can upload to onedrive then share the onedrive link in the reply. 

[Anonymous]

Hi @Catman,
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information. Do not include anything that is unrelated to the issue or question.
Please show the expected outcome based on the sample data you provided.
How to provide sample data in the Power BI Forum - Microsoft Fabric Community

Thank you.

[Catman]

Hopefully this is a bit easier to understand.

Customer	Invoice	Total Amount by Customer	Amount/Total	Amount	Paymentdays	Average Payment Days by Customer
Input	Input	Calculated - sum Amount for Customer	Calculated	Input	Input	Calculated  % * Paymentdays
A	10001	900,000.00	10.2054%	91,848	90	9
A	10002	900,000.00	37.5477%	337,929	45	17
A	10003	900,000.00	4.9367%	44,430	90	4
A	10004	900,000.00	39.5216%	355,695	45	18
A	10005	900,000.00	7.7886%	70,097	90	7
				900,000		55

[Ashish_Mathur]

Hi,

PBI file attached.

[Anonymous]

Hi @Catman ,
I have reviewed your values and performed the weighted average and total amount calculations using DAX .I got the below ouput:

The Weighted Average Payment Days is correctly calculated using the formula: SUMX at the Invoice level, where each Payment Days value is multiplied by the weight of that invoice (Amount divided by the Total Amount by Customer). This results in a weighted average of 55.00, which matches your expectation.
Regarding the Total Amount, the output is 899,999, not 900,000, because when we sum the actual Amount values you provided it is 899,999.Power BI result is accurate based on your data.

Please let us know if you require further help.Glad to help.

Thank you.

[Catman]

Hi, I have tried using the excel file from both solution to calculate the weight % but I keep having the same problem with the Total Amount by Customer. It just shows the individual Amount when I added the InvoiceID instead of aggregating the total.  

So this is what I keep getting as a result:

Customer	Invoice	Amount	Total Amount by Customer	Paymentdays	Amount/Total	Average Payment Days by Customer
A	10001	91,848	91,848	90	100%	90
A	10002	337,930	337,930	45	100%	45
A	10003	44,430	44,430	90	100%	90
A	10004	355,695	355,695	45	100%	45
A	10005	70,097	70,097	90	100%	90
						360

The customer and invoice are 2 separate tables but this shouldn't have any impact, right?

And I can only create measure in the report, and not directly creating the new measures in the data table. 

[Anonymous · Accepted solution]

Hi @Catman ,
Thank you for sharing the update!
It might happening because the total amount by customer is being calculated at the invoice level instead of staying fixed for the customer. This happens when the visual includes invoice-level detail, and the measure does not ignore that context. To get the right weight % and average payment days, the total amount should remain the same across all invoices for a customer. Also, since your Customer and Invoice tables are separate, just make sure there is a proper relationship between them.
Hope this helps.
Thank you.

[bhanu_gautam]

@Catman 

Create a new measure for Total Amount by Customer:

TotalAmountByCustomer = CALCULATE(SUM('Table'[Amount]), ALLEXCEPT('Table', 'Table'[Customer]))

Create a new column for Amount/Total:

AmountTotalPercentage = 'Table'[Amount] / 'Table'[TotalAmountByCustomer]

Create a new column for Weighted Payment Days:

WeightedPaymentDays = 'Table'[AmountTotalPercentage] * 'Table'[Paymentdays]

Create a new measure for Average Payment Days by Customer:

AveragePaymentDaysByCustomer = SUMX('Table', 'Table'[WeightedPaymentDays])

Create a summary table to display the results:
Add a table visual to your report.
Add the Customer column.
Add the AveragePaymentDaysByCustomer measure.
Add the TotalAmountByCustomer measure.

[Catman]

Hi,

By new column, is it the same as creating a new measure?

I can't seem to get the percentage to work. In the working table, the Total Amount shows only the individual amount when I include each invoice and payment Days value. Or does it only work in the summary?

I get the following result, I would expect the Total Amount to be 900,000

Customer	Invoice	Amount	Total Amount	Paymentdays	Amount/Total
A	10001	91,848	91,848	90	100%
A	10002	337,930	337,930	45	100%
A	10003	44,430	44,430	90	100%
A	10004	355,695	355,695	45	100%
A	10005	70,097	70,097	90	100%