Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
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
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.
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
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
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
User | Count |
---|---|
128 | |
108 | |
100 | |
65 | |
62 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |