locked
Creating average cost per claim, bins, and dates in powerpivot RRS feed

  • 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 Charlie Liao Sunday, July 24, 2016 2:51 AM
    • Marked as answer by Charlie Liao Sunday, July 24, 2016 9:46 AM
    Saturday, July 16, 2016 6:37 AM