How to remove full table aggregation from a calculation for percentage

[shelbsassy]

I have a calculated column that is taking the average of all data in the table but I just want to have the percentage by row.  Basically I am trying to divide DataDay by MonthDays for each month to give me a percentage.

The percentage in the calculated column in giving me the aggregate of both Jan and Feb.  I would like it to be Jan = 100% and Feb is 79% but it isgiving me .89 for both rows.

Any insights on how to use a filter?  I have tried numerous options but nothing is working.  Thank you!

[Sean]

Either - get rid of both SUM functions (you don't need to aggregate on each row)

PercentColumn = DIVIDE( TrendCalcs[DataDay] , TrendCalcs[MonthDays], 0 )

Or - wrap each SUM function in CALCULATE to make it respect the row context

PercentColumn 2 =
DIVIDE (
    CALCULATE ( SUM ( TrendCalcs[DataDay] ) ),
    CALCULATE ( SUM ( TrendCalcs[MonthDays] ) ),
    0
)

[shelbsassy]

Awesome thank you, I got that to work.  However when I try to do the final calculation which is the Trended PMPM I am not getting that new column in my intellisense dropdown.

I need to calculate that [Percent Column] * [PMPM]

I have created both PMPM as a measure Members Claims[Total Paid]/Members Claims[# Members]

as well as a column PMPMC = 'Members Claims'[Total Paid]/'Members Claims'[# Members]

and I am trying to create Trended PMPM = [Percent Column]*[PMPM]

Can you see what I am doing wrong?

Thanks so much!

[Sean]

I suspect you don't like the results you are getting in the Total Row

Try these - all 3 are Measures

Percent Measure =
DIVIDE (
    SUM ( 'Members Claims'[DataDay] ),
    SUM ( 'Members Claims'[MonthDays] ),
    0
)

PMPM Measure =
DIVIDE (
    SUM ( 'Members Claims'[TotalPaid] ),
    SUM ( 'Members Claims'[# Members] ),
    0
)

Trended PMPM Measure = [Percent Measure] * [PMPM Measure]

[shelbsassy]

Thank you for your reply.

I think I had my Percent Column backwards, but that is fixed.  I tried what you suggested but the results do not change with the slicer and the numbers don't seem right. 

For example, For Feb, I need to calculate 466.30 PMPM * 1.27 in the Percent Column to give me 592.20 for the Trended PMPM.  I can't see to figure out how to get just that row for that month in the percent column.

[Sean]

Can you post some sample data?

[shelbsassy]

Sample Data

I have scrambled the member information for protection and recreated a workbook to use as a sandbox.

For some reason I am having trouble recreating some of the formulas in my Dates table and TrendCalcs table.  I am getting an error about circular dependency.

I am basically trying to get the latest date of the month the data was refreshed based on service date in order to calculate the trended PMPM.  

What I am trying to accomplish is the trended or projected PMPM (per member per month) based on the claim activity received at that point in time in the month.  I am trying to use the max service date for the month which for Jan is 1/31 and Feb is 2/22.  The PMPM is Total Paid/# Members.  To get the Trended PMPM it is total # days in month/max service date for month * PMPM.

Yesterday I worked on creating a percent column in the trend calc table that divides (for January) 31/31 for 1.00.  So far we have 22 days of data in February so the calculation for Feb is 28/22= 1.27.  So the Feb PMPM (cumulative) is $466.30 (Total Paid/# Members) but the trended should be 466.30 * 1.27 = 592.20 for the February trended PMPM.

I really appreciate any help you can offer.  I have been struggling with this for a week now.  Thank you so much!

@Sean

@CheenuSing

[shelbsassy]

Using the Percent Measure @Sean gave me yesterday as well as the Percent column, it is averaging in the measure but it is summing the column.  I removed aggregation from the ones that I could and this is what it shows me.  I am having a hard time filtering down to just that row of data so the PMPMP would be PMPM*1 for January and for February it would be PMPM *1.27.

This is my Percent Column calc

PercentColumn = CALCULATE(SUM(TrendCalcs[MonthDays])/sum(TrendCalcs[DataDay]),ALLEXCEPT(TrendCalcs,TrendCalcs[FirstDateC]))

[shelbsassy]

I think I might have it.  I have been scouring msdns Dax library and tried this:

Trended PMPM = CALCULATE('Members Claims'[PMPM]) * [Percent Measure](FILTER(TrendCalcs,[FirstDate]))

and I think it is doing what I want it to do. Fingers crossed!

[CheenuSing]

Hi @shelbsassy

Please find attached the modified pbix file. 

https://1drv.ms/u/s!ApP3mBZyGaHfzxy447kb0wpU5XJI

Check the calendar table.

And the change in the Trended table.

Cheers

CheenuSing