Compare the sale of store with sale of region

[tanquoc0309]

Hi all

Store	Sale	Region
A	10000	N
B	20000	N
C	30000	N
D	15000	S
E	19000	S

<tbody>
</tbody>

I created pivot table (powerpivot) with slicer Store.

How can I create a calculate in powerpivot that show Average of Region when I filter Store, such as:
When I choose store "A", the formula will show average of region "N"
When I choose store "C", the formula will show average of region "N"

Thanks,

 

[Kazlik]

One way is to make a calculated column like this

=CALCULATE(AVERAGE(Sheet1[Sale]),FILTER(Sheet1,Sheet1[Region]=EARLIER(Sheet1[Region])))

 

[scottsen]

And as a measure:

Something like this would work:
=IF (HASONEVALUE(Table2[Region]),
CALCULATE(AVERAGE(Table2[Sale]), ALL(Table2), Table2[Region] = VALUES(Table2[Region]))
)

 

[tanquoc0309]

Thanks all, It worked