Forum Discussion
Solved
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 COUNTBL...
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 - StoresWithSales
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