PowerPivot DAX formula to sum on dimension attribute

0
votes

I am trying to do some calculations in a PowerPivot cube that contains data from a time registration system.
I have a table called "Registered Hours" that is connected to a table called "Resource" that again is connected to a table called "Department". I'm trying to build a calculation in the "Registered Hours" table that sums up per Department. The purpose is to compare a resource values against the sum of the entire department.
I've tried implementing the calculation using ALLEXCEPT (from this SO: How to SUM a Column based on another column in DAX Powerpivot):

CALCULATE([Registered Days];  ALLEXCEPT(Resource;'Department'[Department Name]))

This does work, as long as I have the "Department Name" in my pivot table. If I build a pivot table that only has the "Resource Name" (from "Resource" table) on rows this calculation returns the same for all rows. If I add "Department Name" to rows, the calculation works as intended.

1
excelexcel-2013powerpivotdax
Try this: CALCULATE([Registered Days]; ALLEXCEPT(Resource;'Department'[Department Name]), 'Department') – CoolStraw
Thanks for the response, but unfortunately the result is the same. All emloyees get the same value when I don't include the department name in the pivottable. – Søren Sparsø
Soren if you provide me with the excel I will make sure to give you the solution. Unfortunately I lack time to build a similar powerpivot cube and sort it out, but if you send it out it will be easier :-) – CoolStraw
Thanks for helping out. I've created a simple powerpivot cube that outlines the issue. Can I send it to you somehow? – Søren Sparsø
wetransfer.com try this :-) – CoolStraw

1 Answers

1
votes

I suggest creating a calculated field (measure) instead of a calculated column.

It is import to understand the context. If you are not adding a column or row to your pivot table, it will not include it in the context and the calculation for TOTAL will not take that information into account. So, if you want to see the total hours per department and resource, both resource and department must be in the pivot table and both columns should be in the ALL EXCEPT function.