DistictCount StoreCode with Volume in period versus DistictCount StoreCode Universe (Penetration %)

[TKCillie]

Hello everyone, please can you help me?

I have a columns of StoreCodes (TEXT) and their correlating Volume (#) by date.

Based on how the volume fluctuates (Stock in and out by month per Code) I would like to understand what the penetration % is versus the Total Sum Distictcount of StoreCodes IE DistinctCount those with volume present, versus Total Sum of DistictCodes (IE Penentration as a % = Codes with Vol / Codes with and without volume)??

EG January: 5 of 100 stores have stock; penentration percentage is 5%

Any ideas???

I'm really struggling 🙂

[TKCillie]

I removed the *100 so the % shows correctly, but because the filter breaks it down into months - it's always on 100% every month, IE not taking into account Totalcounts for the Year (Total Period) 

[TKCillie]

No unfortunately not, it's still giving me strange %'s 😞

[rohit1991 · Accepted solution]

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)]).

[TKCillie]

THANK YOU SOOOOO MCUH 🙂

[rohit1991]

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.