Forum Discussion
Count Blanks Table
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
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 ) )
4 Replies
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 ) )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 - StoresWithSalesHi,
I have a connection between the tables, so jdbuchanan71 helped me.
Thank you so much for taking time to show an alternative solution.
/depple
It works perfect.
Thx a million!
/depple
