DAX average with different level

[potap]

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 :

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!!!

 

[potap]

Made a little mistake... the average is 5600/6 = 933

 

[ImkeF]

.

 

[ImkeF]

Ooops - where did my post disappear?

Again: Time for a happy measure

CALCULATE(AVERAGE[Cost]; FILTER(ALL(Dim); COUNTROWS(FILTER(Dim; EARLIER(Dim[Department])=Dim[Department]))))

https://javierguillen.wordpress.com/2012/02/06/can-earlier-be-used-in-dax-measures/

 

[potap]

Wow thank you!!! Can't say I really understand Earlier but I'll try with the link you provided.

 

[Kazlik]

Thanks for sharing that link ImkeF.