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!