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!
Solved! Go to 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
@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
Hi @v-ljerr-msft,
For my own reference, the following measure will work per distinct drug and member?
Thanks
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 :-)
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.
Hope this solves the issue.
Regards
Abduvali
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
@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
Hi @v-ljerr-msft,
For my own reference, the following measure will work per distinct drug and member?
Thanks
Find out how to participate in the first Power BI 'Can You Solve These?' challenge.
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
User | Count |
---|---|
97 | |
60 | |
57 | |
57 | |
57 |