Excluding row item from a group calcuation

[masplin]

I would like to compare results from one of my shops with the result form all my other shops EXCLUDING the shop in question.

so I have pivot table with [Location Name] as rows. In values I have [Transactions per hour per store] and I have been able calculate the transactions per hour for all shops using

CALCULATE(                       [Trans per hour per store],ALL(Location[Location Name])
                       )

However what i really want is to calculate this excluding the location on that row. For example

Location Trans/h Trans/h for others
A 10 25 (av B&C)
B 20 20 (A&C)
C 30 15 (A&B)

Is this possible ?

Thanks
Mike

 

[Ozeroth]

Hi Mike,
It sure is possible.

(Note: I'm assuming your [Trans per hour per store] measure will average over all stores in the filter context, otherwise this would need to be changed to an appropriate average in the below measure.)

One possible measure is:

Trans per hour for other locations := CALCULATE([Trans per hour per store],
       FILTER(ALL(Location[Location Name]),
       NOT(  CONTAINS(VALUES(Location[Location Name]),Location[Location Name],Location[Location Name] )  )  )

It uses a FILTER over all locations to include only those not in the current filter context. So it will work for any number of locations in the current filter context.
I have to admit, the repeated references to [Location Name] within CONTAINS look confusing when written out!

Would be interested in others' solutions.

 

[scottsen]

That looks awesome, Ozeroth!

Didn't try it ... at all, but I'm wonder if this slightly different (but same idea) measure would work:

Other Locations := CALCULATE([Trans per hour per store],
       FILTER(ALL(Location[Location Name]),  Location[Location Name] != VALUES(Location[Location Name]))

Probably the VALUES() will end up blowing up on grand totals, so probably:

Other Locations := IF (HASONEVALUE(Location[Location Name]),
       CALCULATE([Trans per hour per store],
       FILTER(ALL(Location[Location Name]),  Location[Location Name] != VALUES(Location[Location Name]))
  )

No better, same idea, and generally pretty cool.

 

[masplin]

Could you explain the "!"?

 

[scottsen]

lol. oooops! Too many languages. the != should be <> (the NOT equal operator)

 

[masplin]

I that case your is slightly more elegant as less code!!! i'll try it thanks