Nested Created Measure: Distinctcount values of one table and match within ranges of another

[Anonymous]

Hi,

I've been working on a dashboard for some time and still struggling getting the resuts of a created measure that I built and need.

I'm trying to find the sum of [Plan Payment Amount] for each distinct [Claim Serial Number].  Both [Plan Payment Amount] and [Claim Serial Number] are within the table 'Claim Data'.  It's important to state that each [Claim Serial Number] can and often does populate numerous times.

I've already gone ahead and created the measures I need in order to find the sum of the total [Plan Payment Amount] for each [Claim Serial Number].  There are simplier ways to do this I'm sure but these are working so I have no issues here. 

Measure1.0 = MAXX(DISTINCT('Claim Data'[Claim Serial Number]),SUM('Claim Data'[Plan Payment Amount]))

Measure1.1 = SUMX(DISTINCT('Claim Data'[Claim Serial Number]),[Measure1.0])

I've also gone ahead and created the stand-alone Ranges table I need called:  'Claim Buckets'

I now need to create one final measure that will allow me to take the [Claim Range] field from the 'Bucket Ranges' table and count the distinct [Claim Serial Number]s that have a total [Plan Payment Amount] that fall within the ranges mentioned above.

Here is a quick table visual with each [Claim Serial Number] with the total [Plan Payment Amount].  Notice how there are 7 [Claim Serial Numbers]s with [Plan Payment Amount]s greater than 100,000.  There are also 2 [Claim Serial Number]s with values between 80,000 and 99,999.  There are also 2 [Claim Serial Number]s with values between 60,000 and 79,999.

 

The measure I created in order to accomplish what I want is this:  

Formula = IF(HASONEVALUE('Claim Buckets'[Claim Min]), countrows(FILTER('Claim Data', [Measure1.1]>=VALUES('Claim Buckets'[Claim Min])&& [Measure1.1]<VALUES('Claim Buckets'[Claim Max]) ) ), COUNTROWS('Claim Data') )

However it is not working correctly.  Instead of it counting the sum of the [Plan Payment Amount] for each distinct [Claim Serial Number]...it is instead only counting the first instance of the [Claim Serial Number] that falls within the range.  For example:

If we had the following:

[Claim Serial Number]     [Plan Payment Amount]

0001                                $81,000

0077                                $75,000

0077                                $10,000

My formula would only count [Claim Serial Number] 0001 being within the 80,000 to 99,999 range.  I need this formula to count both 0001 and 0077 in to the number of [Claim Serial Number]s in this range since 75,000 + 10,000 = 85,000...which is within the 80,000 to 99,999 range.

I thought, that since my created measure Measure1.1 was operating correctly this would work...but it is not.  Can you please help me revise this formula?

Thank you in advance,

Andrew

[Anonymous · Accepted solution]

Hi @Anonymous 

I build two sample tables as below to have a test.

Claim Data Table:

Claim Buckets Table:

Firstly, I calculate the total Plan Payment Amount by measure.

Total Plan Payment Amount = CALCULATE(SUM('Claim Data'[Plan Payment Amount]]]),FILTER(ALLSELECTED('Claim Data'),'Claim Data'[Claim Serial Number]=MAX('Claim Data'[Claim Serial Number])))

Result:

Then I use two measure to count the number of Claim Serial Number which is in the Bucket Range.

M_Claim Range = CALCULATE(MAX('Claim Buckets'[Claim Range]),FILTER('Claim Buckets',[Total Plan Payment Amount]>='Claim Buckets'[Claim Min]&&[Total Plan Payment Amount]<'Claim Buckets'[Claim Max]))

Count = CALCULATE(DISTINCTCOUNT('Claim Data'[Claim Serial Number]),FILTER('Claim Data',[M_Claim Range]=MAX('Claim Buckets'[Claim Range])))

Result:

You can download the pbix file form this link:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/tongzhou_qiuyunus_onmicrosoft_com/EaE7bxDAfR5DpVrEw_a21GcBeTB0zxuLNmensU4nXUMVyg?e=QsyTVL

Best Regards,

Rico Zhou

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

[Anonymous]

Thank you so much.  This is perfect.

[Anonymous]

Bump for views