Forum Discussion
Solved
% Percent Variance/difference from row total by category (columns)
Hi, I'm hoping someone can help, I feel like this should be simple to do but I cannot work it out.
I have a matrix table (1) that shows average scores and I wish to show the % difference of the figure in each column compared to the row total.
So the % difference for question 11a Adult Care and Health figure 3.87 from the row total 3.54 is 9.20%. How can I do this in such a way that I can get it to appear in a table please.
Table 2 in below screenshot is the same table but displayed as '%RT' - the additions in red are what I would actually like to display.
Hi Hawitt
please try one of the following measures
Average Score = VAR AverageScore = AVERAGE ( TableName[Score] ) VAR AverageRowTotal = CALCULATE ( AVERAGE ( TableName[Score] ), ALL ( TableName[Category] ) ) VAR AveragePercent = DIVIDE ( AverageScore, AverageRowTotal ) RETURN IF ( HASONEVALUE ( TableName[Category] ), AveragePercent - 1, AveragePercent )Average Score = SUMX ( VALUES ( TableName[Category] ), CALCULATE ( VAR AverageScore = AVERAGE ( TableName[Score] ) VAR AverageRowTotal = CALCULATE ( AVERAGE ( TableName[Score] ), ALL ( TableName[Category] ) ) RETURN DIVIDE ( AverageScore, AverageRowTotal ) - 1 ) )
3 Replies
Hi Hawitt
please try one of the following measures
Average Score = VAR AverageScore = AVERAGE ( TableName[Score] ) VAR AverageRowTotal = CALCULATE ( AVERAGE ( TableName[Score] ), ALL ( TableName[Category] ) ) VAR AveragePercent = DIVIDE ( AverageScore, AverageRowTotal ) RETURN IF ( HASONEVALUE ( TableName[Category] ), AveragePercent - 1, AveragePercent )Average Score = SUMX ( VALUES ( TableName[Category] ), CALCULATE ( VAR AverageScore = AVERAGE ( TableName[Score] ) VAR AverageRowTotal = CALCULATE ( AVERAGE ( TableName[Score] ), ALL ( TableName[Category] ) ) RETURN DIVIDE ( AverageScore, AverageRowTotal ) - 1 ) )
