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
Sean's avatar
Sean
Community Champion
9 years ago

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

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's avatar
    Sean
    Community Champion
    9 years ago

    Can you post some sample data?

    • 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]))