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

[shelbsassy]

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!

[Ashish_Mathur]

Hi,

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

[shelbsassy]

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

[parry2k · Accepted solution]

what is the relationship between table where you have your Actual AMPM Measure and table in which you have % column?

[shelbsassy]

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!

[Ashish_Mathur]

Hi,

Upload your file to Google Drive and share the download link here.

[shelbsassy]

Hi @Ashish_Mathur

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

[Ashish_Mathur]

Hi @shelbsassy,

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

[shelbsassy]

Ill have to create a dummy dashboard with data due to PHI info on this one.

[v-huizhn-msft]

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]

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.