Power BI is turning 10, and we’re marking the occasion with a special community challenge. Use your creativity to tell a story, uncover trends, or highlight something unexpected.
Get startedJoin us for an expert-led overview of the tools and concepts you'll need to become a Certified Power BI Data Analyst and pass exam PL-300. Register now.
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.
This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.
Check out the June 2025 Power BI update to learn about new features.
User | Count |
---|---|
73 | |
71 | |
55 | |
38 | |
31 |
User | Count |
---|---|
71 | |
64 | |
60 | |
50 | |
45 |