Expression to use for 'Comparable' Locations

[msawyercke]

Good Morning!

I load data for each of our locations that contains sales performance.  It is structured like this:

Unit #     

Current Year Sales ("CY TP 13")   

Year Ago Sales   ("PY TP 13") 

From there, I use the following formula to highlight % Change Vs Ya

AVG ([CY TP 13])/avg([PY TP 13])-1

The problem is, I need to create a chart (perhaps a TreeMap), that only displays the data of 'comparable' locations, since some locations that are reporting sales this year weren't open last year, distorting the % change statistic.  In other words, I want to calculate locations where PY TP 13>0.

In my example, what would the expression be in the expression editor?

Thanks for any help that can be provided?

[msawyercke]

I sum CY, then Sum LY, then use the formula (Sum CY/Sum LY)-1

[msawyercke]

[sunny_talwar · Accepted solution]

Try this:

Sum({<[Comp_Flag] = {1}>} [CY TP 13])/Sum([PY TP 13]) - 1

[msawyercke]

Outstanding!!  Thank You, Sunny!!