Forum Discussion

novotnajk's avatar
novotnajk
Icon for Resolver I rankResolver I
4 years ago
Solved

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

 

$ Cumulative Medical Spend =
CALCULATE([$ Medical Spend],
FILTER (
ALL ('Reporting'[reporting_date]),
'Reporting'[reporting_date] <= MAX('Reporting'[reporting_date] )
))
 
This formula doesn't work.  HELP #1 Please! πŸ™‚
 

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)

 

$ SL Medical Spend = CALCULATE (
SUMX (
FILTER (
SUMMARIZE ( Medical, Medical[special_id], "Aggregate", [$ Medical Spend] ),
[Aggregate] >= [$ SL]
),
[Aggregate]
)
)+0
 
This isn't working either.  HELP #2!  PLEASE!
 
I don't know why they aren't working.  Any guidance would be helpful!!!

19 Replies

  • Hi novotnajk 

     

    Can you share your PBIX file [after removing sensitive data]?

     

    Appreciate your Kudos!!

     

  • I am unable to share the pbix unfortunately because it is tied to millions of health records

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity Champion

    Try this:

    novotnajk 

     

    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's avatar
      Nathaniel_C
      Icon for Community Champion rankCommunity 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's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity 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's avatar
      novotnajk
      Icon for Resolver I rankResolver 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 _calc
       
      But 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's avatar
        Nathaniel_C
        Icon for Community Champion rankCommunity 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's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity 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's avatar
      novotnajk
      Icon for Resolver I rankResolver I

      Hi Nathaniel,

      Step 1:  Removing CALCULATE from the $ Medical Spend was done.

      It now reads:  

      $ Medical Spend = (SUM(Medical[cost])
      • novotnajk's avatar
        novotnajk
        Icon for Resolver I rankResolver 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 _calc
         
        This did not work.  See screenshot.

         

  • Nathaniel_C's avatar
    Nathaniel_C
    Icon for Community Champion rankCommunity 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.
    Nathaniel

    Per 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 )
    



  • Hi Nathaniel!

     

    We fixed all but one of the problems!  πŸ™‚  I'm going to repost the outstanding issue.  Thanks for your help!