Forum Discussion
depple
Helper III
Helper IIICount 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
Super User
Super UserIf 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
)
)
MitchMResolver II
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
deppleHi,
I have a connection between the tables, so jdbuchanan71 helped me.
Thank you so much for taking time to show an alternative solution.
/depple
- depple
It works perfect.
Thx a million!
/depple