average default of payment per customer

[MS2121]

Hello everybody

I have measure that calculates the average default of payment by invoice.

I would like to have a table with invoices and the average default of payment for the certain customer (to see whehter I can expect a receipt of payment).

When I add the measure to a visual (table) with the invoices it shows the average by invoice (which is empty because there has not been a payment yet).

Is it possible to build a measure that calculates the average default of payment for the customer that belongs to an invoice (not for the invoice itself)? If it works there the value cannot be empty if the customer has ever paid any invoice.

Thank you very much 🙂

[123abc]

Certainly! It seems like you want to calculate the average default of payment for each customer based on their invoices. To achieve this, you'll need to create a new measure that calculates the average default of payment for each customer. Assuming you have a table named "Invoices" with columns like "CustomerID," "InvoiceAmount," and "DefaultOfPayment," you can create the following DAX measure:

DAX Measure:

AverageDefaultOfPaymentPerCustomer =
CALCULATE(
AVERAGE('Invoices'[DefaultOfPayment]),
ALLEXCEPT('Invoices', 'Invoices'[CustomerID])
)

Here's what this measure does:

1. CALCULATE: It modifies the context in which the calculation is made.
2. AVERAGE('Invoices'[DefaultOfPayment]): It calculates the average of the "DefaultOfPayment" column in the "Invoices" table.
3. ALLEXCEPT('Invoices', 'Invoices'[CustomerID]): It removes all filters from the "Invoices" table except for the "CustomerID" column. This ensures that the average is calculated for each customer.

Now, you can use this measure in your table visual alongside the "CustomerID" and other relevant columns. It will show the average default of payment for each customer based on their invoices.

Make sure to adjust the table relationships and column names in the DAX measure according to your data model. If you have a different table structure or column names, you might need to modify the measure accordingly.

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

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.