Handling Data That is non-existent to calculate as 0

[dswinden]

I have a dataset that i will filter by;

Month Start (start of the month)

Territory Name (Name of our sales territory)

Account Name (Name of the retailer)

SKU Name (Name of the SKU being measured)

I want the measure to show up as a 1 or 0 (1 = In Stock, 0 = Out of stock).

There will be 1000's of examples where there is no data for a SKU at a given store (example below) due to the fact that the store has never ordered this product.  I want this instance to show as a 0

The screen shot below will hopefully help;

The YELLOW section is a sample of what my data looks like.  

The BLUE section is a simple excel logic

if(Sumifs()>0,1,0)

I have tried;

if(INVENTORY>0,1,0), and this only works for stores where there is data

I have tried COALESCE a few different ways, same result.  SWITCH, same result.

Any other ideas?

[RSC]

Hi @dswinden,

If you already created a measure which is counting INVENTORY, then that is fine.

We just need to add one more condition to the if statement, which is:

if(INVENTORY>0 && INVENTORY<>BLANK(),1,0)

 
I think this measure can help you,

Still facing issue, fell free to ask...

Thanks!

[dswinden]

Hi @RSC Thanks for your reply, but still gives me blank cells instead of 0/1

If i don't filter the dataset at all, then i can at least visually see the store name and a blank next to it.

However, if i were to filter my example to "SKU 1" i would only see three stores instead of the four with a 0 next to Joseph's store

The use case for this is for a salesperson to be able to see all of their stores regardless of the SKU it is filtered to with 1 (listed) and 0 (not listed) next to it