Forum Discussion

keylorbb's avatar
keylorbb
New Member
8 years ago
Solved

marketshare

Hello, I have a database of more than 5 million lines with billing information, each line with the customer code that you bought, that is, you can have 1 line for one customer while another customer can have 10 lines. I want to calculate the percentage of penetration of a product in a certain period, the percentage of penetration is calculated: how many customers bought me at least 1 product "x" among the total number of customers who bought in that period, that is, in January, they sold me 100 clients but only 10 customers bought at least one fragrance, my fragrance penetration is 10%. thank you very much

Need Help
  • Anonymous's avatar
    Anonymous
    8 years ago

    HI keylorbb,

     

    I' m not so sure which product are yours, I write a sample formula below, maybe you can take a look.

    Measure =
DIVIDE (
    CALCULATE (
        COUNTROWS ( Table1 ),
        VALUES ( Table1[MES] ),
        Table1[Marca] IN { "CYZONE", "GENERICA" }
    ),
    CALCULATE ( COUNTROWS ( Table1 ), VALUES ( Table1[MES] ) ),
    -1
)

     

    I assume Marca column stored tags to mark owner, "CYZONE", "GENERICA" are yours.

    Values function will auto filter records to let formula calculate on current category, when you use MES column as group column.

     

     

     

    Regards,

    Xiaoxin Sheng

3 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    8 years ago

    HI keylorbb,

     

    I'd like some sample data with similar table structure to test and coding formula.(not need too many records) It is hard to coding formula without any same data.

     

    Notice: do mask on sensitive data before sharing.

     

    Regards,

    Xiaoxin Sheng

  • keylorbb's avatar
    keylorbb
    New Member
    8 years ago

    Thank you very much for the response.

     

    This is a fragment of the data.

     

    what I need to divide the number of customers billed in the filter that is selected from the number of total customers of another filter. For example, 1000 customers bought at least one fragrance in 201806 month and we had 4549 total invoices, how to do this same exercise if you wanted to select a specific area? how many customers in that area bought a fragrance and what is the total order of that area.

     

    MESPaísRegiónZonaSecciónSegmento Comportamiento Rolling (Desc. Corta)Código de clienteProductoMarcaClaseForma de VentaCódigo de Tipo de OfertaTipo de OfertaTipo de Medio de VentaCantidad de Pedidos Puros (Real)Cantidad de Pedidos Total País (Real)Venta Neta MN (Real)Venta Catálogo D MN (Real)Unidades Vendidas (Real)Unidades Demandadas (Real)Oportunidad de Ahorro cliente MN (Real)
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752CY CABLE GET CONNECTEDCYZONEHOGARCATALOGO015OFERTA SUPER ESPECIALCATALOGO145499.7112.99112.6
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752CY GOLDEN STAR SET ANICYZONEBIJOUTERIECATALOGO015OFERTA SUPER ESPECIALCATALOGO145499.112.99113.25
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752CZ CYSOLUX MASC PUN NEG 90G FMCYZONETRATAMIENTO FACIALCATALOGO015OFERTA SUPER ESPECIALCATALOGO145493.855.5111.38
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752EXPOFERTAS PA 2018 C-07GENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752FLYER DEVOS MAGNETIC C07 PAGENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB BASE CONCENTRE MEDIUM 6LBELMAQUILLAJECATALOGO029DEMO PROD LINEA - CONCURSO / FESTIVALREVISTA1454917.7518.991114
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB EFP SOMB KIT CEJAS 4.4 G FMLBELMAQUILLAJECATALOGO212PRODUCTO EN SET - GRAN VENTA ONLINEMV WEB1454948.6525527.95
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB ESS DEMQ CC 125 MLLBELTRATAMIENTO FACIALCATALOGO030PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA REVISTA14549001117.99
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB ESS DEMQ CC 125 MLLBELTRATAMIENTO FACIALCATALOGO048DEMO PROD LINEA - NIVELESREVISTA1454928.0229.973324
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB NOCT SERUM CC TS 5MLLBELTRATAMIENTO FACIALAGREGADO I077MUESTRAS PARA REGALO - PEDIDOSOTROS1454900110
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752LB PALETA LL VELOUR BARRA 6.8GLBELMAQUILLAJEAGREGADO I126VENTA MUESTRAS - PORTAL VIRTUALOTROS145493.733.99110
    201806PanamáREGION 31 PANAMA3108FConstantes 3000000752SOBRE DE CAMPANA PA 2018 C-08GENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CATALOGO CYZONE PA 2018 C-08CYZONEMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CATALOGO LBEL PA 2018 C-08LBELMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CY BOLSO AGATHACYZONECOMPLEMENTOSCATALOGO115DEMO NUEVO y CIRCULOS DE CALIDAD INDIVIDUALREVISTA1454923.3624.991110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CY ESM KHALI FIN BLING EMERALDCYZONEMAQUILLAJECATALOGO013OFERTA MAXIMACATALOGO145493.154.5111.13
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CY LL MAD 4 RED PSYCHOCYZONEMAQUILLAJECATALOGO114PROMOCIÓN PROPIA BLOQUE COMISIONABLE FIJACATALOGO145492.433.25110.65
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CZ CYSOLUX MASC PUN NEG 90G FMCYZONETRATAMIENTO FACIALCATALOGO015OFERTA SUPER ESPECIALCATALOGO145493.855.5111.38
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CZ DEL BROWN 4 EVER PICYZONEMAQUILLAJECATALOGO013OFERTA MAXIMACATALOGO145493.54.99111.25
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CZ DEL SOFT MORACYZONEMAQUILLAJECATALOGO015OFERTA SUPER ESPECIALCATALOGO145493.154.5111.13
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590CZ FLIRTY SEXY PROB C/C 4.5 MLCYZONEFRAGANCIASAGREGADO III082MUESTRAS EN LIQUIDACIÓNOTROS1454900220
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590EXPOFERTAS PA 2018 C-07GENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590FLYER DEVOS MAGNETIC C07 PAGENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB COLLAGENESSE INTENSIVO 15MLLBELTRATAMIENTO FACIALAGREGADO III093REGALOS ESPECIALESOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB DEL COLOR INTENSE NOIRLBELMAQUILLAJECATALOGO015OFERTA SUPER ESPECIALCATALOGO145495.67.99112
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB DESOLBEL DES DRY 48 50 MLLBELCUIDADO PERSONALCATALOGO015OFERTA SUPER ESPECIALCATALOGO145493.855.5111.38
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB LL EFP HIDR CC MALVELBELMAQUILLAJELIQUIDACIONES021OFERTA ADICIONALCATALOGO145494.485.99111.2
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB MATISSE CR SENSU PT 400 MLLBELTRATAMIENTO CORPORALCATALOGO030PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA REVISTA14549001118.99
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB MITHROSE PARF 50 MLLBELFRAGANCIASCATALOGO001DEMO NUEVO y CIRCULOS DE CALIDADREVISTA1454937.3639.982225.02
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB MITHROSE PARF MINI 10 MLLBELFRAGANCIASCATALOGO030PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA REVISTA14549001110.5
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB NOCT SERUM CC TS 5MLLBELTRATAMIENTO FACIALAGREGADO I077MUESTRAS PARA REGALO - PEDIDOSOTROS1454900110
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590LB REVE SENS PARF 50MLLBELFRAGANCIASCATALOGO202OFERTA INDIVIDUAL - OFERTA PARA TIMV WEB1454921.9623.51116.49
    201806PanamáREGION 31 PANAMA3120GConstantes 2000000590SOBRE DE CAMPANA PA 2018 C-08GENERICAMATERIAL APOYO MKTAGREGADO III057MATERIAL IMPRESO PARA OBSEQUIOOTROS1454900110
    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      HI keylorbb,

       

      I' m not so sure which product are yours, I write a sample formula below, maybe you can take a look.

      Measure =
DIVIDE (
    CALCULATE (
        COUNTROWS ( Table1 ),
        VALUES ( Table1[MES] ),
        Table1[Marca] IN { "CYZONE", "GENERICA" }
    ),
    CALCULATE ( COUNTROWS ( Table1 ), VALUES ( Table1[MES] ) ),
    -1
)

       

      I assume Marca column stored tags to mark owner, "CYZONE", "GENERICA" are yours.

      Values function will auto filter records to let formula calculate on current category, when you use MES column as group column.

       

       

       

      Regards,

      Xiaoxin Sheng