How to get total cost savings by distinct drug name and member ID with an if statement

[shelbsassy]

I have an IF statement that I thought was working fine until I just realized it is calculating the same drug cost savings for every instance so the numbers are inflated.

We have drugs that we switch to lower cost drugs and I have a column with the Monthly Cost Savings if they accept the switch.

Some members hhave more than one intervention where we have reached out to them multiple times.  If they accept the recommendation, the If statement is:

Monthly Cost Savings = IF(Sheet1[status_id]=1 || Sheet1[Status_id = 4,Sheet1[Monthly Cost Savings Table],0)

I need to somehow differentiate to only add the cost savings once per distinct drug per member.

Does anyone have any idea how to do this?  I tried to just add a filter to the end of the statement but it errored out.

Thanks for any help!

[v-ljerr-msft · Accepted solution]

Hi @shelbsassy,

If I understand you correctly, you should be able to use the formula below to create a new measure to calculate the total cost savings by distinct drug name and member ID, then show the measure on the Card visual in your scenario. 

Measure =
SUMX (
    SUMMARIZE (
        Sheet1,
        Sheet1[drug name],
        Sheet1[member ID],
        "Costs", MAX ( Sheet1[Monthly Cost Savings] )
    ),
    [Costs]
)

Regards