Forum Discussion
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 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!
9 Replies
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 )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!
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]
