Hi!
I am looking for a way to calculate the average cost by department without having to drag this field in the pivot table or as a slicer.
Let's say I have this pivot table that has a slicer on region 1 :
<tbody>
</tbody>
My fact table looks like this :
<tbody>
</tbody>
My fact table as a relationship with this dimension table :
<tbody>
</tbody>
The new column has to calculate the average cost of unit A, B, C and D (shown in the pivot table) but also unit H and I (same department).
If these are the total cost of region 3 :
<tbody>
</tbody>
The new column should show 700 as the average cost of department XX
<tbody>
</tbody>
Thanks for your help!!!
I am looking for a way to calculate the average cost by department without having to drag this field in the pivot table or as a slicer.
Let's say I have this pivot table that has a slicer on region 1 :
Unit | TotalCost |
A | 1000 |
B | 1600 |
C | 800 |
D | 1200 |
<tbody>
</tbody>
My fact table looks like this :
OrderNum | Date | Unit | EENum | Cost |
<tbody>
</tbody>
My fact table as a relationship with this dimension table :
Unit | Region | Department |
A | 1 | XX |
B | 1 | XX |
C | 1 | XX |
D | 1 | XX |
E | 2 | YY |
F | 2 | YY |
G | 2 | YY |
H | 3 | XX |
I | 3 | XX |
<tbody>
</tbody>
The new column has to calculate the average cost of unit A, B, C and D (shown in the pivot table) but also unit H and I (same department).
If these are the total cost of region 3 :
Unit | TotalCost |
H | 400 |
I | 600 |
<tbody>
</tbody>
The new column should show 700 as the average cost of department XX
Unit | TotalCost | DeptAverage |
A | 1000 | 700 |
B | 1600 | 700 |
C | 800 | 700 |
D | 1200 | 700 |
<tbody>
</tbody>
Thanks for your help!!!