Count Number of Sales Voids (0 sales) in store by product

[Tuan]

I'm trying to count the numbers of products that have no sales at a store. I'm trying to go through a virtual table route, not working. What would be the best way of going about this.

Count of Stores with Zero Product Sales = 
COUNTROWS(FILTER(SUMMARIZECOLUMNS('Product'[Product_Description],Store_List[STORE NAME]),[Net Sales]=0))

I'm actually using another formula to visual the voids. All i want is to Count the Voids

Void = SWITCH(TRUE(),[Net Sales]>0,"Stock",[Net Sales]=0,"Void")

  Any help would be much appreciated.

[ryan_mayu]

@Tuan 

could you please provide the sample data and the expected output?

[Tuan]

This is a sample dataset and result. My solution didn't work when selection different products.

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[Tuan]

I want to filter by Region/Week/Product and also use it in a Table. As you can see from the file it gives incorrect item voids counts when displayed in a table.

Attached is the link

https://drive.google.com/file/d/1PApr9ZmMPvGNP2kbY0LlfI0rKIIXD-yN/view?usp=sharing

[Ashish_Mathur]

Hi,

I cannot understand.  Please let me know the exact result you want to see in the Table.

[Tuan]

I think i figure it out, not exactly the best way since it doesn't work with certain filters.

Essentially it cross filters the store/product dimension and look for cells that have no data and then count the rows. I'm sure there many ways to do this. I was trying to do it using a virtual table via Calculatetable or SummarizedColumns,

Item Voids = 
COUNTROWS(EXCEPT (
    CROSSJOIN ( VALUES ( Store_List[STORE NAME] ), VALUES ( 'Product'[Product_Description]) ),
    SELECTCOLUMNS (
        'Daily Data',
        "Customer", 'Daily Data'[Store],
        "Product", 'Daily Data'[Product_Description]
    )
))

[amitchandak]

@Tuan , Try like

Count of Stores with Zero Product Sales =
count(FILTER(SUMMARIZE('Product','Product'[Product_Description],Store_List[STORE NAME],"_1",[Net Sales]),[_1]=0))

Flag =
Maxx(SUMMARIZE('Product','Product'[Product_Description],Store_List[STORE NAME],"_1",[Net Sales]),if([Net Sales]>0,"Stock","Void"))

[Tuan]

Doesn't work for me, says function only accepts a column reference.