Reusable DAX measure with different slicers

[serky]

Hello
I am having trouble writing a measure that works correctly for different slicers. I have 2 tables: Table 1 - store and area; Table 2 - program, store, # events, # calls (see below as could not attach a file).

Company performance = # events (stores in East and West) plus # calls (stores in North)

I need to report this measure in 3 different tables: total performance; performance by area; performance by program. I am trying to write a single measure and re-use it but am not having any luck.

To keep it simple, I am initially trying to write measures for the correct # events only (syntax below is rough only for simplicity):

[Performance] = sum[# events]
[Total performance]=CALCULATE([Performance],stores<>"North")

This works correctly for reporting the total but when I slice by an area, the filter overwrites the slicer. I have tried re-writing this formula different ways eg - using ALLEXCEPT etc but am not having any luck. What is the best way to tackle this?

Thanks

[TABLE="width: 279"]
<colgroup><col><col span="3"></colgroup><tbody>[TR]
[TD]Table 1[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Store[/TD]
[TD]Area[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]1[/TD]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]2[/TD]
[TD]North[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]3[/TD]
[TD]East[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]4[/TD]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]5[/TD]
[TD]West[/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Table 2[/TD]
[TD][/TD]
[TD][/TD]
[TD][/TD]
[/TR]
[TR]
[TD]Program[/TD]
[TD]Store[/TD]
[TD]# Events[/TD]
[TD]# Calls[/TD]
[/TR]
[TR]
[TD]Dancing[/TD]
[TD]1[/TD]
[TD]3[/TD]
[TD]21[/TD]
[/TR]
[TR]
[TD]Dancing[/TD]
[TD]2[/TD]
[TD]4[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Painting[/TD]
[TD]1[/TD]
[TD]5[/TD]
[TD]57[/TD]
[/TR]
[TR]
[TD]Fashion[/TD]
[TD]3[/TD]
[TD]3[/TD]
[TD]32[/TD]
[/TR]
[TR]
[TD]Painting[/TD]
[TD]3[/TD]
[TD]2[/TD]
[TD]61[/TD]
[/TR]
[TR]
[TD]Car maintenance[/TD]
[TD]4[/TD]
[TD]3[/TD]
[TD]29[/TD]
[/TR]
</tbody>[/TABLE]

 

[Kazlik]

Have you tried something like this?

performance:=CALCULATE(SUM(Table2[Events]),FILTER(Table1,Table1[Area] <> "North"))

 

[serky]

Hi

just realised I typed an error in my original formula which may have confused the issue It should have been:
[Total performance]=CALCULATE([Performance],Table1[area]<>"North")

I thought that this was a simple filter and should give the same answer as your formula but it doesn't. Your formula works perfectly - thankyou.

Can you explain why they are different? Is a filter in CALCULATE only considered simple if it's filtering using a column from the same table as the measure?

Thanks

 

[scottsen]

If you see this : Multi-Column Performance in Power Pivot there is some discussion on the meaning of the simpler form of calculate. But basically, there is an implied ALL()