Forum Discussion

TKCillie's avatar
TKCillie
Regular Visitor
1 year ago
Solved

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

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 🙂

Need Help
  • rohit1991's avatar
    rohit1991
    1 year ago

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

     

     

5 Replies

  • rohit1991's avatar
    rohit1991
    Super User
    1 year ago

    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.

     

     

  • TKCillie's avatar
    TKCillie
    Regular Visitor
    1 year ago

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

     

    • rohit1991's avatar
      rohit1991
      Super User
      1 year ago

      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's avatar
        TKCillie
        Regular Visitor
        1 year ago

        THANK YOU SOOOOO MCUH 🙂

         

  • TKCillie's avatar
    TKCillie
    Regular Visitor
    1 year ago

    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)