Forum Discussion

shelbsassy's avatar
shelbsassy
Resolver I
9 years ago

How to remove full table aggregation from a calculation for percentage

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 mont...
Need Help
shelbsassy's avatar
shelbsassy
Resolver I
9 years ago

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

Anonymous

 

 

 

shelbsassy's avatar
shelbsassy
Resolver I
9 years ago

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's avatar
    shelbsassy
    Resolver I
    9 years ago

    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!