Forum Discussion
DistictCount StoreCode with Volume in period versus DistictCount StoreCode Universe (Penetration %)
Hi TKCillie
You’re getting 100% because your denominator is being filtered by the month on the axis.
For each month, you’re counting stores that appear in that month in both the top and the bottom, so the ratio becomes 1.Fix: make the denominator ignore the month but still respect higher-level slicers (e.g., Year, Region). Use ALLSELECTED over your Calendar/Date table.
Stores With Vol = CALCULATE( DISTINCTCOUNT( 'Data'[StoreCode] ), KEEPFILTERS( 'Data'[Volume] > 0 ) )Total Stores (Selection) = CALCULATE( DISTINCTCOUNT( 'Data'[StoreCode] ), ALLSELECTED( 'Date'[Date] ) )Penetration % = DIVIDE( [Stores With Vol], [Total Stores (Selection)] )-
Put Month from your Calendar table on the axis, and make sure that table is related to 'Data'[Date] and marked as a date table.
-
If you want the denominator to be your entire store universe regardless of dates, create a separate Stores dimension and use:
Total Stores (Universe) = DISTINCTCOUNT( Stores[StoreCode] ), then Penetration % = DIVIDE([Stores With Vol], [Total Stores (Universe)]).
-
Hi TKCillie
You can calculate penetration % by dividing the number of stores that have volume in the selected period by the total number of stores, then multiplying by 100.
Stores_With_Volume =
CALCULATE(
DISTINCTCOUNT('Table'[StoreCode]),
FILTER('Table', 'Table'[Volume] > 0)
)
Total_Stores =
DISTINCTCOUNT('Table'[StoreCode])
Penetration % =
DIVIDE([Stores_With_Volume], [Total_Stores], 0) * 100
-
Stores_With_Volume : Counts only stores where volume is greater than 0.
-
Total_Stores : Counts all unique stores.
-
Penetration % : Calculates the percentage.