5 Replies Latest reply on Mar 22, 2018 11:19 AM by Eric Hammond

# Count of Stores with Zero Sales

I am trying to build a report that shows how many stores got zero sales. It is organized by Territory (a Territory has many stores in it). It will look kind of like this, count of stores in each territory, how many of those stores had 0 sales and the percentage of their stores that have zero sales:

At first I thought this would be easy, maybe it still is and I am just not getting it. But I cant figure out how to count the number of stores that have zero sales.

I tried putting in a filter for when sum(sales) us 0, but that only works if I have all the stores listed. But I only want the Territories listed. Any ideas? I have a feeling that we may need a Level of Detail Calc but I am rather lost on those.

Thanks again in advance for everyones help!

• ###### 1. Re: Count of Stores with Zero Sales

Hi Daniel,

It may be easier to count the stores with sales:

[with Sales] = COUNTD([StoreID])

[Territory Stores] = {FIXED [Territory]: COUNTD([StoreID])}

[% of Stores without Sales] = ([Territory Stores] - [with Sales]) / [Territory Stores]

1 of 1 people found this helpful
• ###### 2. Re: Count of Stores with Zero Sales

Hi Daniel,

Pl See the screenshot and attached. Just make changes in formula.

Thanks

Deepak

1 of 1 people found this helpful
• ###### 3. Re: Count of Stores with Zero Sales

Daniel,

It's hard to say without seeing your data.  If you can post a packaged workbook (even if it has fake data) that would be helpful.

Based on what you've shared it sounds like LOD could be helpful.  Your view is at the territory level but you want your calculation done at the store level.  So you might need something like {Fixed [store]: SUM( IF [sales]>0 THEN 0 ELSE 1 END)} .  If you have date filters on your view, you'll need to add those to context so it takes into account the time period you're looking at (or any other dimension filter) before doing the calculation.

Hope that helps.

-Hope

1 of 1 people found this helpful
• ###### 4. Re: Count of Stores with Zero Sales

Thanks to both of you!

Hope I think that that worked for me. But I do have date filter. I have it filtered to last week only. How do I put that in the LOD calculation?

• ###### 5. Re: Count of Stores with Zero Sales

Just kidding, I got it. I just had to right click on the Date filter and click add to filter. Thanks for everyones help!