Forum Discussion
Aggregate Patient Claims when Exceeding Threshold
Hi PBI Gurus! Need some help. Here's my dilemma:
Part 1 - Table
Here is my data table example
Special ID reporting_date $ Medical Spend
ABC123 1/1/2021 $25,000
CDF123 2/1/2021 $15,000
ABC123 2/1/2021 $15,000
XYZ123 2/1/2021 $10,000
ABC123 3/1/2021 $5,000
The Reporting Dates are an append from multiple tables and in a separate table called 'Reporting'[reporting_date])
I created a measure called Medical Spend from the medical table $ Medical Spend = CALCULATE(SUM('Medical'[medical_cost])
Part 2 - Table with Limits
I have a table with values that are in $5K increments call 'ISL' [isl_limit]. I created a slicer visual that allows me to select a value (i.e., $5,000, $25,000, etc.).
Part 3 - Total Medical Spend
I created a table with the Reporting Date and the medical spend by month:
Quarter Month $ Medical Spend
Q1 January $25,000
Q1 February $40,000
Q1 March $5,000
Help #1 - Cumulative Total Medical Spend
I created a formula that I would hope create the following results, but it does not work:
Quarter Month $ Cumulative Medical Spend
Q1 January $25,000
Q1 February $65,000
Q1 March $70,000
Help #2 - Cumulative Total Medical Spend that Exceeds the ISL
First I created a measure called $ SL = CALCULATE(SUM('ISL' [isl_limit]) and created a drop down filter
Second, I created a second measure that I would hope show the cumulative amounts of members who exceed the $SL value, thus producing a table like this (where the SL value is for total medical costs for all members > $25K in spend):
Quarter Month $ Cumulative Medical Spend $ SL Medical Spend
Q1 January $25,000 $25,000
Q1 February $65,000 $40,000
Q1 March $70,000 $40,000
Special ID ABC123 Spent $25K in January, another $15K in February and nothing in March (total $40K)
Hi novotnajk
Here is my .pbix https://1drv.ms/u/s!AgCd7AyfqZtE3zw8aPY2ndPJl7a4?e=mmShevI believe that this solves both questions,
Thank you,
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
NathanielHi Nathaniel!
We fixed all but one of the problems! π I'm going to repost the outstanding issue. Thanks for your help!
19 Replies
- VahidDM
Super User
- novotnajk
Resolver I
I am unable to share the pbix unfortunately because it is tied to millions of health records
- Nathaniel_C
Community Champion
Try this:
Running Total = var _currentDate = MAX('$ Medical Spend'[Reporting Date]) var _calc = CALCULATE([Medical Spend],FILTER(ALL('$ Medical Spend'[Reporting Date]),'$ Medical Spend'[Reporting Date] <=_currentDate)) return _calc
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel- Nathaniel_C
Community Champion
Here is a pic of the table. And you don't need to use CALCULATE() in your first measure
Medical Spend = SUM('$ Medical Spend'[ $ Medical Spend])
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel- Nathaniel_C
Community Champion
Hi novotnajk ,
The issue is that in your query, you use the MAX() date. You need to capture the date on the row that is currently being evaluated before you use the CALCULATE() and refer to it in your FILTER.Hopes this helps,
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel
- novotnajk
Resolver I
Hi Nathaniel,
Thanks for the advice. I created the measure below:
$ Cumulative Medical Spend =var _currentDate = MAX('Medical'[reporting_date])var _calc = CALCULATE([$ Medical Spend],FILTER(ALL('Medical'[reporting_date]),'Medical'[reporting_date] <=_currentDate))return _calcBut it didn't show all of the claims based on the ISL slicer. Also, the Medical Spend measure has the calculate in front because the data change each month. Does that matter?I also can't get the $ SL Medical Spend formula do cumulate based on the filtered value either. I created a sample PBIX, but not sure how to attach it here.- Nathaniel_C
Community Champion
Hi novotnajk ,
Save your pbix to your one drive, create a link, copy it, and then using the link symbol, here in the interface, paste it.
- Nathaniel_C
Community Champion
Hi novotnajk ,
So taking this one step at a time, did we solve issue number 1?
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel- novotnajk
Resolver I
Hi Nathaniel,
Step 1: Removing CALCULATE from the $ Medical Spend was done.
It now reads:
$ Medical Spend = (SUM(Medical[cost])- novotnajk
Resolver I
Step 2 for Cumulative Medical, the formula is this:
$ Cumulative Medical Spend =var _currentDate = MAX('Reporting'[reporting_date])var _calc = CALCULATE([$ Medical Spend],FILTER(ALL('Reporting'[reporting_date]),'Reporting'[reporting_date] <=_currentDate))return _calcThis did not work. See screenshot.
- Nathaniel_C
Community Champion
Hi novotnajk
Try this. And I resaved my .pbix, so try clicking on the link again.
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
NathanielPer user = VAR _specialID = MAX ( '$ Medical Spend'[Special ID ] ) VAR _calc = CALCULATE ( [Running Total - ISL], FILTER ( ALL ( '$ Medical Spend'[Special ID ] ), '$ Medical Spend'[Special ID ] = _specialID ) ) RETURN IF ( _calc > 0, _calc, 0 ) - Nathaniel_C
Community Champion
Hi novotnajk
Here is my .pbix https://1drv.ms/u/s!AgCd7AyfqZtE3zw8aPY2ndPJl7a4?e=mmShevI believe that this solves both questions,
Thank you,
Let me know if you have any questions.
If this solves your issues, please mark it as the solution, so that others can find it easily. Kudos πare nice too.
Nathaniel - novotnajk
Resolver I
Hi Nathaniel!
We fixed all but one of the problems! π I'm going to repost the outstanding issue. Thanks for your help!