Need my Denominator to include more records than my Join can see

[KarHut]

Hello Community --

I work with medical claims data, and a key metric is "Amount Paid Per Member Per Month."

* The numerator is the total dollars paid (in a given period, like Jan thru Mar).

* The denominator is total member months (total member months Jan thru Mar = members in Jan + members in Feb + members in Mar).

I have two key datasets:

* tbl_Enrollment

* tbl_Claims

I've had to generalize and randomize it quite a bit, but here's an .XLSX file with sample data.

https://drive.google.com/file/d/15ozRHA9UYWCGuv1BlOd1CnrdKikziUjd/view?usp=sharing

(In my actual data files, I have about 660 rows in tbl_Enrollment, and 26 million rows in tbl_Claims.)

I have a unique key in tbl_Enrollment, and a key of the same values in tbl_Claims. The join/relationship is Many to One, Cross filter direction = Both.

Here's the trouble: I need to get the numerator from the Claims table, and the denominator from the Enrollment table. But not every Enrollment group has a claim in every category every month. So when my visual filters for Group "Charlie" in YrMo "201908" with Category "X" and no claims records meet all the criteria, it does not include the Members for that month in the Sum of Member_Count. I need to include all the appropriate Member_Counts even when the join doesn't return any Claims.

The correct denominator values in this SAMPLE data set look like this:

But when I show the sum of Paid_Amt (from Claims) and sum of Member_Count (from Enrollment), the Member_Count values are limited to the records found in the table join. What I want to see is: for 2019, Alpha had 30025 members (regardless of the Claims category).

The column Paid_PMPM is a simple measure:

Paid_PMPM = SUM(tbl_Claims[Paid_Amt])/SUM(tbl_Enrollment[Member_Count])

But they are not correct, because the Sum of Member Count values are missing Enrollment records that need to be included.

I hope I haven't gone off the rails here! Please ask questions if I need to clarify anything. I sure appreciate any help.

[Anonymous]

Hi @KarHut ,

My understanding is that you want to sum all the rows in a table, or all the values in a column, ignoring any filters that might have been applied. If my understanding is correct, please try to use the ALL function.

Here's a simple sample.

Without ALL function = SUM('Table'[Value])

With ALL function = CALCULATE(SUM('Table'[Value]),ALL('Table'))

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[KarHut]

Hi @amitchandak -- thanks so much for your kind reply. It's my first post in this forum.

Is this the best way to share files here? It's a link to my sample PBIX document.

https://drive.google.com/file/d/1-YxlPtv8SR2BwqJxkWYHxxa0LtsIUVSE/view?usp=sharing

In my original post I included a link to an Excel data file with my two tables. (I tried to limit my 26M row data file to an 80 row X 20 column sample, but it still seemed too big for a table in the post. If an in-post table is better, I'd be happy to do that.)

I may not have said this earlier, but I also need to be able to filter the data by the various other dimensions in the Claims table. Some are hierarchical, like Category 1 and 2. When any of them are included in the visual, they reduce the number of joined Enrollment records and return a reduced Member Count sum.

Here are the correct, high-level Enrollment totals in a table. (I'm having a really hard time with table formatting in these posts.)

Sum of Member_Count		Year
LOB	Bus_Group	2019	2020
Commercial Group	Alpha	30025	22032
Commercial Group	Bravo	38618	32776
Commercial Group	Charlie	211298	161742
Commercial Group Total		279941	216550
Individual	Delta	37125	26214
Individual	Echo	154194	118856
Individual	Foxtrot	9042	6972
Individual Total		200361	152042

Here's a sample output for Alpha group in 2019, with wrong Member Count values from PowerBI, and the values I want to get:

LOB	Bus_Group	Categ_1	Sum of Paid_Amt (2019)	Sum of Member_Count (2019)	CORRECT Member_Count	CORRECT Paid PMPM
Commercial Group	Alpha	Institutional Rad/Lab			30025	0
Commercial Group	Alpha	Other OP	$496.90	18006	30025	0.0165
Commercial Group	Alpha	Other Primary Care	$83.60	5833	30025	0.0028
Commercial Group	Alpha	Other Prof Services			30025	0
Commercial Group	Alpha	OV - PCP			30025	0
Commercial Group	Alpha	Prof Rad/Lab	$143.50	6186	30025	0.0048
Commercial Group	Alpha	Surgery - Hospital	$99.90	5833	30025	0.0033

Thanks again!

[AlB]

Hi @KarHut 

Why don't you share the pbix you've built with the mock data, instead of the loose tables in the excel file?? That would save us time and the guesswork and it seems you already have built it since you are showing the visuals.

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers 

[amitchandak]

@KarHut ,Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

[KarHut]

Hello. Giving this a bump, to ask if the PBIX file and info I added is helpful. Thanks.