locked
How can I summarize Across Tables only filtering by 1 row label? RRS feed

  • Question

  • I have two tables:

    Table 1

    Prov Group  Mbr_id Months

    Med Group 1 000001 11

    Med Group 1 000002 12

    Med Group 2 000003 12

    ...

    AND

    Table 2

    Prov_Group  Mbr_id TOS PAY_AMT

    Med Group 1 000001 ER  $1000

    Med Group 1 000001 Inpatient $10000

    Med Group 1 000002 PCP Visit 1200

    Med Group 1 000002 ER $1300

    Med Group 2 000003 Inpatient $5000

    The two tables are related by MBR_ID.

    I want to create a pivot table that shows:

    PROV_GROUP TOS PAY_AMT Months PMPM

    Med Group 1  ER $2300 23 $100.00

    Where the Months field in the pivot table is summarized across PROV_GROUP and Pay Amount is summarized by PROV_GROUP and TOS.

    I'm having real problems figuring this out even though it seems very easy.

    THANKS!

    Friday, July 27, 2012 7:20 PM

Answers

  • eao94001 -

    I used your data above and built a linked-table model.  Seems to be producing what you're asking for.  Here are the steps I walked through.

    1. create relationship from Mbr_id in table 2 to Mbr_id in table 1

    2. create these calculated measures:

    SumMonths:=SUM(Table1[Months])
    
    SumPayAmt:=SUM(Table2[Pay_Amt])
    
    PayAmtPerMonth:=IF([SumPayAmt] > 0 &&  [SumMonths] > 0,
                                             [SumPayAmt] / [SumMonths],
                                             BLANK()
                                            )

    Create a pivot table with:
    - Prov Group from Table 1 on the Rows axis
       (make sure you don't use the Prov Group in Table 2 since that's on the many side of the relationship and will not be able to slice Months appropriately)
    - TOS from Table 2 on the Rows axis
    - All 3 measures in the Values area

    Should look like this:

    Let me know if that helps, or if I misunderstood your question.  The main catch is slicing by Provider Group from Table1.  Could actually simplify this with a cleaner, more dimensional data model, but this approach will work with what you've got.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Saturday, July 28, 2012 4:36 AM
    Answerer

All replies

  • Is my question written poorly?
    Saturday, July 28, 2012 12:56 AM
  • eao94001 -

    I used your data above and built a linked-table model.  Seems to be producing what you're asking for.  Here are the steps I walked through.

    1. create relationship from Mbr_id in table 2 to Mbr_id in table 1

    2. create these calculated measures:

    SumMonths:=SUM(Table1[Months])
    
    SumPayAmt:=SUM(Table2[Pay_Amt])
    
    PayAmtPerMonth:=IF([SumPayAmt] > 0 &&  [SumMonths] > 0,
                                             [SumPayAmt] / [SumMonths],
                                             BLANK()
                                            )

    Create a pivot table with:
    - Prov Group from Table 1 on the Rows axis
       (make sure you don't use the Prov Group in Table 2 since that's on the many side of the relationship and will not be able to slice Months appropriately)
    - TOS from Table 2 on the Rows axis
    - All 3 measures in the Values area

    Should look like this:

    Let me know if that helps, or if I misunderstood your question.  The main catch is slicing by Provider Group from Table1.  Could actually simplify this with a cleaner, more dimensional data model, but this approach will work with what you've got.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com



    Saturday, July 28, 2012 4:36 AM
    Answerer
  • First of all, thanks very much for responding.

    This seems to be working at the PROV_NM level but at the aggregate TOS level it seems off. I think it is calculating member months incorrectly at the TOS subtotal level.

    But, even if that isn't working - this is fantastically helpful!

    Thank you!

    -Eric

    Saturday, July 28, 2012 1:03 PM
  • Course, first let me make sure I did this right.
    Saturday, July 28, 2012 1:03 PM
  • Ok - it IS working.  I added a report filter and that breaks it.  I didn't mention that there aer 4 different datasets in my table and I need to be able to run this for each one.
    Saturday, July 28, 2012 1:05 PM
  • Glad that helped.  I noticed that I had TOS above ProviderGroup on the Rows axis.  I pulled ProviderGroup to the top, and believe that matches your requirement more closely.  Updated the image above to reflect this.  The months for the ProviderGroup are now repeated across their related TOS buckets.

    Also, regarding your need to filter, whatever you're filtering by would need to be in Table1 given your current model.  That's because the filter needs to apply to the aggregation in Months directly in Table1 and across the relationship to the aggregation in PayAmt in Table2.  If you're trying to filter by a field in Table2, the filter won't naturally apply to Table1 because of the direction of the relationship (Table1 is a lookup that should return only 1 row for each row in Table2).

    Please provide more details about your filtering requirement if you're still stuck on that.


    Brent Greenwood, MS, MCITP, CBIP
    // Please mark correct answers and helpful posts //
    http://brentgreenwood.blogspot.com


    Saturday, July 28, 2012 2:17 PM
    Answerer
  • The field is in both Table 1 and Table 2.  It appears to work on SumPayAmt, but not on SumMonths.

    Saturday, July 28, 2012 6:38 PM
  • I added a second filter variable from Table 1 and now, if I set both to the same setting I get the right answer.  Ugly, but it works.
    Saturday, July 28, 2012 7:16 PM