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
)
)
MitchM
Resolver II
Resolver IIjdbuchanan71'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