Calculating Across Tables

[Excelsior88]

Hi All-

I'm sure this topic has been covered, so even a link would suffice. I have two tables in my data model. One is a detail of every claim (I work for an insurance company) and the other is a summary of our membership by client by month. I want to create values in a per member per month (PMPM) format. Ideally, I would like to display in my Power View a summary of the amount paid by client and put that over the membership and have it all be drillable by month. I am getting stuck trying to figure out the easiest way to join the tables, because I can do it on service month or client. Additionally, when I put the membership into the model it only lets me sum by client, put does not allow me to create a chart visualition just tables.

I am new to data modelling so any advise is greatly appreciated!

Thanks,

Excelsior88

 

[scottsen]

Probably a paste of a few rows of each table might help us visualize what is going on.

In your case I suspect you might benefit from adding a calendar table. Then you can relate both claims and member info to the calendar table. Dropping fields from the calendar table (say a YearMonth field) should let you get what you want?