Average for multiple rows for same customer

[Anonymous]

1) We have a file with loans per customer. We are displaying this file in a table visualization. It shows:

Customer Name, Customer Unique ID, Loan Amount.

Since some customers have 2 loans, we have 34 rows of loans, but only 30 unique Customer ID's in those 34 loans.

2) We copy and paste this same section (basically duplicating it) and change the visualization to a 'Card' We then display the Loan Amount on that card, and change the property from 'Sum' to 'Average'. It now displays the average amount per Customer. But what we want it the average amount per loan. Since there are less customers than loans, the average amount displayed is too high.

How do we fix that? How do we get an average per loan?

[waltheed · Accepted solution]

Hi broekman,

You will need to add some calculated measures to your data model.

Like these:

Count of Loans:=COUNTROWS('LoanTable')

Count of Customers:=DISTINCTCOUNT([Customer ID])

Avg by Loan:=AVERAGE([Loan Amount])

Avg by Cust:=DIVIDE(SUM([Loan Amount]); [Count of Customers]; BLANK())

These will give you the right numbers.

[Anonymous]

Thanks !!