cancel
Showing results for
Did you mean:
Highlighted
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
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.

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

Regards

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.

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

Regards

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
Established Member

Hi @shelbsassy,

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?

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.

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 :-)The Cost savings is bottom leftCost savings is towards the right

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

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

Established Member

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

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

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.

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

Regards

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.

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

Regards

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

Announcements

#### Challenge: Can You Solve These?

Find out how to participate in the first Power BI 'Can You Solve These?' challenge.

#### Community News & Announcements

Get your latest community news and announcements.

#### Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

#### Win Power BI Swag with Community Kudopalooza!

Each week, complete activities and be qualified in the drawing for cool Power BI Swag.

Top Kudoed Authors
Users Online
Currently online: 339 members 3,981 guests
Recent signins: