Forum Discussion

Hawitt's avatar
Hawitt
Frequent Visitor
4 years ago
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 f...
Need Help
  • tamerj1's avatar
    tamerj1
    4 years ago

    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
    )
)

     

tamerj1's avatar
tamerj1
Community Champion
4 years ago

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
    )
)

 

Hawitt's avatar
Hawitt
Frequent Visitor
4 years ago

Thank you so much, I will give those a go now.