Pivot Table Sum Column for Measure Total

Question

I have a problem on my hands. I'm learning DAX and I'm having a tough time with a problem that involves aggregating absolute values. In a Pivot Table I want to be able to have a ratio of (absolute value of ((# of Employees) - (# of Recommended Employees))) / (# of Recommended Employees). This will show me how far off actuals are from recommended for an area. To do so, I created each of these as measures in Power Pivot. This picture explains it better than I can:

Notice how for Canada Total, the Absolute Value is the sum of the row, not the sum of the column. I want the area ratio to be the sum of all the absolute values for each Standard Title divided by the # of Recommended Employees for the area. So for Canada, the ratio should be (167+60+109)/2556 = 13.15%. Here are the measures I'm using:

Employees := SUM(EmployeesActual)
Recommended Employees := SUM(EmployeesRecommended)
Absolute Value := ABS([Employees]-[Recommended Employees])
Ratio := [Absolute Value] / [Recommended Employees]

Any advice here would be much appreciated!

score 0 · Answer 1 · edited Apr 25 '16 at 09:26

Recommended Employees Area:=
CALCULATE(
    [Recommended Employees]
    ,ALL( 'your table name'[StandardTitle] ) 
    // It is a best practice to always use fully qualified
    // column references
)

Here, we evaluate your (impossible - you're using bare strings) [Recommended Employees] measure, having stripped the filter context from the [StandardTitle] field. This gives you the total for the filter context coming from the remaining row labels.

Pivot Table Sum Column for Measure Total

1 Answers1