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

Join 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.

Reply
shelbsassy
Resolver I
Resolver I

How to create a measure from 2 columns using DIVIDE but needing a filter to get row context

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!

1 ACCEPTED 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.

View solution in original post

10 REPLIES 10
Ashish_Mathur
Super User
Super User

Hi,

 

Your question is not clear.  Please share your file and show the expected result.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

 

 

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Hi @Ashish_Mathur

Here is the link, hope you can help.  Pharmacy Dashboard

Hi @shelbsassy,

 

That is a very heavy file.  Please delete some data and get it down to a couple of MB's.  Thank you.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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

shelbsassy
Resolver I
Resolver I

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.

 

 

 

 

pic.jpg

Helpful resources

Announcements
Join our Fabric User Panel

Join our Fabric User Panel

This is your chance to engage directly with the engineering team behind Fabric and Power BI. Share your experiences and shape the future.

June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

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