Adding Absolute Value

[jdun]

Hi PBI Community,

    I am trying to come up with a function, expression, or method to display the total of the absolute value column in the below matrix. Var S - F is the variance between forecast and sales, which is sometimes negative. Var % S-F/F is the percent variance between forecast and sales. I didn't have any trouble getting absolute value to work for the individual items in the Absolute Value Variance and Absolute Value Variance % columns, but I want something that will add up the absolute value variance instead of just giving me a positive of the sales variance. The combined absolute value variance of the below three items should be 43,424, not 12,496. Currently, the overall is still just taking the sales variance and making it positive instead of doing 27,960 + 3,244 + 12,220. Similarly, the Absolute Value Variance % should be 17%, not 5%.

Ideally, I would like to display this value in the matrix, but separate scorecards for the absolute value variance and the absolute value variance percentage would work too.

[DataInsights · Accepted solution]

@jdun,

You can try a totals measure like this:

Ab Val Var Totals =
SUMX ( VALUES ( Items[Item] ), [Ab Val Var] )