Forum Discussion
Calculating Weighted Average
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.
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.
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 |
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.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.
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.
Hi,
PBI file attached.
