I'am struggling with a DAX measure (or calculated column maybe?) for getting the difference between 2 columns in a one-to-many relationship.
Here's an example
A table contains required activities divided in tasks (task 1 and task 2).
Another table is fullfilled with employees who work part of their time on the different tasks.
I am asked to provide the difference between the task required times (in required_activities table) and actual times (in employee table)
data model:
tables:
So I would like to get a Pivot table like this one:
How to build the measure for calculating the difference (employee real times - required_activities times)
? I tried using RELATED
function but can't make it work.
There are two methods, Method 1 will get you going faster but is limited - whereas Method 2 needs a little effort to shape your data.
Method 1 answers your question directly.
Method 2 is the recommended approach.
If there are a smallish, finite number of tasks then try the following:
Add a new table to your data model. Let's call it task
that looks like:
Task |
---|
task 1 |
task 2 |
task 3 |
Single column table with a row for each of your task's name. No relationship needed.
Next, add these three measures to your model.
required:=
var task1 = SUM(required_activites[task 1])
var task2 = SUM(required_activites[task 2])
var task3 = SUM(required_activites[task 3])
RETURN SWITCH( SELECTEDVALUE('task'[Task]),
"task 1", task1,
"task 2", task2,
"task 3", task3,
task1 + task2 + task3
)
real:=
var task1 = SUM(employees[task 1])
var task2 = SUM(employees[task 2])
var task3 = SUM(employees[task 3])
RETURN SWITCH( SELECTEDVALUE('task'[Task]),
"task 1", task1,
"task 2", task2,
"task 3", task3,
task1 + task2 + task3
)
diff:= [real] - [required]
Updated
Note: If your version of Excel doesn't support
SELECTEDVALUE
then replace it with:
RETURN SWITCH( IF( HASONEVALUE('task'[Task]), VALUES('task'[Task]) ) ,
And that should do it. Ensure you are adding 'task'[Task]
in your Pivot table Rows.
The other method requires altering the shape of your data model and you will need to use Power Query to do this. This method is the recommended approach as it will scale and perform optimally. (Lookup 'Star schema')
For both of your tables, select all the task columns and unpivot them so you get a structure like this:
activity id | Task | Value |
---|---|---|
a | task 1 | 0.5 |
a | task 2 | 0.5 |
b | task 1 | 0.2 |
b | task 2 | 0.3 |
Updated
While in PowerQuery, select your
required_activities
query/table (the one that you've unpivoted), right-click andDuplicate
. Rename this new query toDim Activity
.
- Right-click on the
activity id
column header, and selectRemove Other Columns
.- And one more time, right-click on the column header and select
Remove Duplicates
Again, select your
required_activities
query/table, right-click andDuplicate
. Rename this new query toDim Task
. Follow the same steps so that you are left with a Task column without duplicates. (Note, this step removes the need for the earliertask
table mentioned in Method 1.In your Data Model (Power Pivot), create relationships like so:
Once you have that, the DAX measures become super simple:
required:= SUM(required_activites[Value])
real:=SUM(employees[Value])
diff:= [real] - [required]
Updated
In your Pivots and Charts, use
Dim Activity
&Dim Task
for your rows/columns etc...