Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
shelbsassy
Resolver I
Resolver I

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
v-ljerr-msft
Employee
Employee

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

View solution in original post


@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

View solution in original post

14 REPLIES 14
v-ljerr-msft
Employee
Employee

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

Thank you so much that worked PERFECTLY!  Exactly what I was looking for!  Thank you!


@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

I just realized I think I need the IF statement in there somewhere because I only want to calculate if the intervention was accepted.  I tried Test 2 = IF(Sheet1[status_id] = 1 || Sheet1[status_id] = 4,[Cost Test],0) where Cost Test is the formula you recommended that works so I just need to figure out how to only calculate if the status ID is 1 or 4.  

I am getting this error when I try to use the formula I posted:  Test 2 = IF(Sheet1[status_id] = 1 || Sheet1[status_id] = 4,[Cost Test],0)

 

A circular dependency was detected: Sheet1[Test 2], Sheet1[Pending #_], Sheet1[Test 2].

 

I also tried  Test 2 = HELP = IF(Sheet1[status_id] = 1 || Sheet1[status_id] = 4,[Cost Test],SUMX(Summarize(sheet1,Sheet1[Drug Name],Sheet1[PAT_Primary_Identifier],"Cost Savings",MAX(Sheet1[Monthly Cost Savings])),[Cost Savings]))

 

but I am getting this error: A circular dependency was detected: Sheet1[HELP], Sheet1[Pending #_], Sheet1[HELP].

 

Looks like you had the same idea as v-ljerr-msft which worked just perfect!  Thank you so much for your time in helping me!  I really appreciate it!

No, @v-ljerr-msft should be getting all the credit for providing the correct solution =D

Abduvali
Skilled Sharer
Skilled Sharer

Hi @shelbsassy,

 

 

Can you send a screenshot of your data, please.

 

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

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

Heres both.  Hope you can make something out of it 🙂The Cost savings is bottom leftThe Cost savings is bottom leftCost savings is towards the rightCost savings is towards the right

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

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

 

 

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.