cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
shelbsassy Member
Member

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

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!

2 ACCEPTED SOLUTIONS

Accepted Solutions
v-ljerr-msft Super Contributor
Super Contributor

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

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. Smiley Happy

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

 

Regards

Abduvali Established Member
Established Member

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


@v-ljerr-msft wrote:

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. Smiley Happy

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

 

Regards


Hi @v-ljerr-msft,

 

For my own reference, the following measure will work per distinct drug and member? 

  • Which means one card with the following measure per drug or member, right

 

Thanks

14 REPLIES 14
Abduvali Established Member
Established Member

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

Hi @shelbsassy,

 

 

Can you send a screenshot of your data, please.

 

shelbsassy Member
Member

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

Thanks for your quick reply!  Should I send screenshot of data view or dashboard view?

Abduvali Established Member
Established Member

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

Data view would be better but if you can do both.

shelbsassy Member
Member

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

Heres both.  Hope you can make something out of it :-)Screen1.pngThe Cost savings is bottom leftscreen2.pngCost savings is towards the right

Abduvali Established Member
Established Member

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

I think you can get away without creating an IF statement just by calculating Monthly Cost Savings try this:

 

create measure:

Monthly Cost Savings =

calculate(sum(Sheet1[Monthly Cost Savings Table]), Sheet1[status_id]=1") + calculate(sum(Sheet1[Monthly Cost Savings Table]), Sheet1[Status_id = 4)

 

There are probably other ways of doing this but I this should do the trick.

 

Let me know how you got on.

 

 

Regards

Abduvali

shelbsassy Member
Member

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

That measure worked the same as the if statement but I am still not sure how to only calculate the cost for the distinct drug per member

 

Abduvali Established Member
Established Member

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

 

My bad I thought your statement didn't provide the right amount.

 

You won't be able to display individual drug and member in one CARD, you need to apply filters to display that information but you already have everything in place to do it by using dynamic nature of Power BI.

Exp: Click on one Drug in your Donut Chart and it will update the Cost-Saving Measure, now if you want to see by member add another chart/table/slicer with members in it and when you select certain member all other charts will update accordingly.

  • Another way to achieve this is to display all that information in a single Table but it will take up a big part of the page to do it. 

 

Hope this solves the issue.

 

 

Regards

Abduvali

v-ljerr-msft Super Contributor
Super Contributor

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

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. Smiley Happy

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

 

Regards

Abduvali Established Member
Established Member

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


@v-ljerr-msft wrote:

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. Smiley Happy

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

 

Regards


Hi @v-ljerr-msft,

 

For my own reference, the following measure will work per distinct drug and member? 

  • Which means one card with the following measure per drug or member, right

 

Thanks