Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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
Solved! Go to 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 ) )
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 ) )
It works perfect.
Thx a million!
/depple
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
User | Count |
---|---|
103 | |
90 | |
78 | |
72 | |
68 |
User | Count |
---|---|
110 | |
96 | |
95 | |
74 | |
71 |