Problem using SUMX to iterate a table

[barnettjacob]

Hi,

I trying to write a measure to help me work out 'Like for Like' customer traffic in our stores. To do this I need to adjust this year's number so that I am only adding the traffic after the counter has been installed for a year.

To help me do this I have an additional column on the Store table that gives the date of the first traffic count - therefore I am aiming to filter my calendar table for dates that are 364 days greater than that number for each store.

I had hoped the measure below would work but it doesn't as the total seems to ignore the individual stores and basically uses the overall 'First Footfall' as opposed to iterating by store.

=CALCULATE(SUMX(Footfall,Footfall[traffic]),FILTER('01 - Calendar','01 - Calendar'[Date]>=min('03 - Store'[First Footfall])+364))

Any assistance would be appreciated.

Jacob

 

[powerpivotpro]

Send me a workbook, even one with fake data, and I will dig in happily

 

[barnettjacob]

Thanks Rob, workbook sent.
Jacob

 

[barnettjacob]

OK. With Rob's help I got to the answer.

The technique is detailed in his blog post here: Subtotals and Grand Totals That Add Up “Correctly” « PowerPivotPro

Effectively for the totals I had to create a measure that added the footfall just for the relevant dates then pass it through a separate SUMX to iterate it by store as desired:

[Footfall]=SUM(Footfall,Footfall[traffic])

[Adj Footfall]=CALCULATE([Footfall],FILTER('01 - Calendar','01 - Calendar'[Date]>=min('03 - Store'[First Footfall])+364))

[Footfall Fixed]=SUMX(VALUES('03 - Store'[Store]),[Adj Footfall])

I can then use this handy little COUNTROWS device to choose whether to use the standard [Footfall] measure or the one that has been iterated by store depending on whether it is a single store or a total:

[Footfall TY LFL] = 
IF(COUNTROWS(VALUES('03 - Store'[Store]))=1,
   [Footfall],
   SUMX(VALUES('03 - Store'[Store]), [Footfall Fixed])

)

Thanks Rob!!!!

 

[barnettjacob]

Correction:

[COLOR=#333333]
[Footfall TY LFL] = [/COLOR]IF(COUNTROWS(VALUES('03 - Store'[Store]))=1,   [Footfall],   [Footfall Fixed]) [COLOR=#333333])
[/COLOR]