Hello,
I am trying to get the percent difference on a crosstab report and I am close but not sure how to make the Data Item calculate correctly. report layout below:
Total PMPM |Current Count| Prev Count| Difference | Percent Difference
group1 $20 $5 $15 15/37
group2 $30 $3 $27 27/37
group3 $0 $0 $0 0/37
Total $50 $8 $37 8/50
I need to calculate the total Difference total into each row. Is this possible and how would I go about doing that. I have tried current count - previous count / total(current count - previous count). that does not work.
Is this due to the fact I am using a cube to get my measure of Total PMPM?
Thanks for the help
Quote from: CognoiseDan on 30 Mar 2016 03:15:08 PM
Hello,
I am trying to get the percent difference on a crosstab report and I am close but not sure how to make the Data Item calculate correctly. report layout below:
Total PMPM |Current Count| Prev Count| Difference | Percent Difference
group1 $20 $5 $15 15/37
group2 $30 $3 $27 27/37
group3 $0 $0 $0 0/37
Total $50 $8 $37 8/50
I need to calculate the total Difference total into each row. Is this possible and how would I go about doing that. I have tried current count - previous count / total(current count - previous count). that does not work.
Is this due to the fact I am using a cube to get my measure of Total PMPM?
Thanks for the help
Hi,
Is there a logical hierarchy for the group members in the rows - ie do they all have a single parent? If so, you could perhaps bring this in as the total row, and use a Tuple of this and your difference calculation to get the percent?
Cheers!
MF.
Hello,
I was able to figure out how to solve for this. in the filter for Percent Difference i used - [Current count - Previous count] / total([current count - previous count] within set [Group]) - Group being the row level of what i am calculating everything on with the measure in a crosstab report.
Hope that makes sense
CognoiseDan