Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
100 | |
78 | |
64 | |
58 |
User | Count |
---|---|
148 | |
113 | |
97 | |
84 | |
67 |