Count Blanks Table

[depple]

Hi, 

I have a dataset with a table containing all stores and another table with sales. Not all stores have sales. How do I count the number of stores with no sales? I have looked into the COUNTBLANK, but I don't think it can help me, given my data structure.

My dataset is like this

Table A

|Store|

Store A

Store B

Store C

Store D

Store E

Table B

|SalesID|Store|Quantity|

01 Store C 10

02 Store D 5

03 Store A 20

How do I count the number of stores with zero sales? In the example above, the answer should be 2 (Store B and E).

Any help will be highly appreciated.

/depple

[jdbuchanan71 · Accepted solution]

If you have a measure that calculates sales.

Sales = SUM ( 'Table B'[qty] )

You can use that in a COUNTROWS measure over your stores

No Sales = 
CALCULATE(
    COUNTROWS( 'Table A' ),
    FILTER(
        'Table A',
        [Sales] = 0
    )
)

[depple]

It works perfect.

Thx a million!

/depple

[MitchM]

jdbuchanan71's solution works perfect as long as you are able to relate the two tables. If you cannnot relate the tables in the data model you can try this:

Stores with No Sales = 
VAR TotalStores = DISTINCTCOUNT( 'Table A'[Store] )
VAR StoresWithSales =
    CALCULATE(
        DISTINCTCOUNT( 'Table A'[Store] ),
        TREATAS( 
            SUMMARIZE( 'Table B', 'Table B'[Store] ),
            'Table A'[Store]
        )
    )
RETURN
    TotalStores - StoresWithSales

[depple]

Hi,

I have a connection between the tables, so @jdbuchanan71 helped me.

Thank you so much for taking time to show an alternative solution. 

/depple