Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
I am trying to create a measure that I can get the Trended Per Member Per Month but the measure I have for Percent is not working.
I tried doing this measure:
Trended PMPM = ROUND((CALCULATE('Members_Claims'[Actual PMPM]) * [Percent](FILTER(TrendCalcs,[FirstDate]))),2)
which in the example should be $145.09 * 3.75 (data day is 8 so 30/8 = 3.75). However I am getting $165.49 (with a percent of 1.14.) I figured out what its doing is adding percent for months 1-12 (1,1,1,1,1,1,1,1,1,1,3.75,3.85) but then averaging months 1-11. I only want the 3.75 row for November. The correct result should be $145.09 * 3.75 = $559.08 for the trended pmpm.
My Percent Measure is :
Percent = CALCULATE(DIVIDE(SUM('Trend'[MonthDays]),SUM('Trend'[Data Day])),FILTER(TrendCalcs_New,TrendCalcs_New[FirstDateC]))
Apparently something is going on with my filters but I am not very good at filtering in dax.
Do you have any suggestions on how I can get the calculation to be $559.08?
Thank you for your help!
Solved! Go to Solution.
what is the relationship between table where you have your Actual AMPM Measure and table in which you have % column?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Hi,
Your question is not clear. Please share your file and show the expected result.
I have a measure for Actual PMPM = [TotalPaid]/[# Members] which is [ $40043.95 ] /[276] which is $145.09. Per Member
I have a column that is calculating the percent which is Trend[MonthDays] divided by Trend[Data Day] which gives me a row in the column which the value of 3.75. I need a way to multiply that row (for November) by the measure for Actual PMPM. I can't seem to do it trying any way of filtering. It needs to be filtered for the month whcih is the column Trend[FirstDateC] for 11/1/2017 and the way I have it now it is adding up te rows in the percet column for all the months and then averaging them for a value of 1.14 but it is supposed to be 3.75.
The screenshot above is of the Trend table to hopefully clarify. If you tell me how to upload my workbook I can do that but I don't see where I can attach a file.
Thanks
what is the relationship between table where you have your Actual AMPM Measure and table in which you have % column?
Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!
Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo
If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤
Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.
Thanks for the nudge @parry2k I had created that table and not formed a relationship. As soon as I did it worked perfectly. Thank you!
Hi,
Upload your file to Google Drive and share the download link here.
Hi @shelbsassy,
That is a very heavy file. Please delete some data and get it down to a couple of MB's. Thank you.
Ill have to create a dummy dashboard with data due to PHI info on this one.
Hi @shelbsassy,
Do you mind simplify your sample as @Ashish_Mathur suggested? It's will be helpful to find a solution.
Best Regards,
Angelia
Basically I just need a measure that I can somehow get the 3.75 value from the Percent Column for Novemeber and multiply it by the measure for Actual PMPM which is Actual PMPM = Total Paid/# of Members. I have tried everything I can think of and nothing seems to work.
User | Count |
---|---|
72 | |
66 | |
34 | |
25 | |
22 |
User | Count |
---|---|
96 | |
94 | |
59 | |
45 | |
42 |