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.
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.
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%
