Firstly thanks for any help in advance. The attached workbook and spreadsheet are samples that I created similar to what I am working. Unfortunately I cannot upload the real dashboard so this is a scaled down version.
Here is what I am working on. The data that I am working with has 3 tables - Plan, ClaimsSubmitted and Drugs. This is sample medical data. So for a given company they can have many plans as well as many claims. The Plan and ClaimsSubmitted tables have a many to many relationship with the join being on 2 columns as seen in the spreadsheet - Year_mo and Groupid. The data is at a month level as you will see in the spreadsheet. THen you have a claim which will be for a certain medicine/drug. That is joined via NDC_CD which is the national codes for drugs.
The goal is to get amount per member per drug. This would be done by getting total Amount for the claims for each drug and then dividing by the MEMBERS column. SUM(AMOUNT)/SUM(MEMBERS) .The problem is I think because of way Tableau handles many to many the MEMBERS calculation doesn't work. It comes up with way too many MEMBERS. From the Plan tab in the sample spreadsheet attached you can see the MEMBERS Total should only be 1601 but Tableau is showing 30000 or something like that.
I'm guessing there is an LOD solution but I haven't been able to figure it out.