# Creating average cost per claim, bins, and dates in powerpivot

• ### Question

• Hello. I would like to create an average cost per claim, based on a condition, put into bins and filtered by dates. Spreadsheet attached  Thanks.

Table 1

 Claim Expense Status Date 1 12 Paid 1/1/2011 1 20 No 1/1/2011 2 40 No 2/1/2011 2 27 No 2/1/2011 3 71 Paid 3/1/2011 3 92 Paid 3/1/2011 4 53 Paid 4/1/2011 4 10 Paid 4/1/2011 5 33 Maybe 5/1/2011 6 45 IDK 6/1/2011 7 19 No 7/1/2011 8 42 Paid 7/2/2011 8 83 Paid 7/2/2011 8 67 No 7/2/2011 9 70 Paid 8/1/2011 9 14 Paid 8/1/2011 9 74 No 8/1/2011

 Unique Claims Sum 1 12 3 163 4 63 8 192 9 158

 Bins # of 1 to 50 1 51 to 150 1 151 to 200 3

Monday, July 11, 2016 10:43 PM

### Answers

• Hi Alex,

You can achieve this requirement in Power BI desktop easily.
Power BI Desktop

1. Create a calculated column in original table
SumExpense = CALCULATE(SUM('Average'[Expense]),FILTER(ALLEXCEPT('AVERAGE','Average'[Claim]),'Average'[Status]="Paid"))
2. Create a new table
Table 2 = SUMMARIZE('Average','Average'[Claim])
3. Create two calculated column in new table
SumExpense = LOOKUPVALUE('Average'[SumExpense],'Average'[Claim],'Table 2'[Claim])
Column 2 = IF(ISBLANK('Table 2'[SumExpense]),blank(),IF(AND([SumExpense]>=0,[SumExpense]<=50),"1 to 50",IF(AND([SumExpense]>50,[SumExpense]<=150),"50 to 150","151 to 200")))
4. Use a table visual to display the result.

Regards,

Charlie Liao
TechNet Community Support

• Proposed as answer by Sunday, July 24, 2016 2:51 AM
• Marked as answer by Sunday, July 24, 2016 9:46 AM
Saturday, July 16, 2016 6:37 AM