# How can I summarize Across Tables only filtering by 1 row label?

• ### 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

• 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
http://brentgreenwood.blogspot.com

Saturday, July 28, 2012 4:36 AM

### 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
http://brentgreenwood.blogspot.com

Saturday, July 28, 2012 4:36 AM
• 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).