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

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 :smileyhappy:

 

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

 

 

shelbsassy's avatar
shelbsassy
Resolver I
9 years ago

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'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?