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.
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
Solved! Go to Solution.
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
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.
MES | País | Región | Zona | Sección | Segmento Comportamiento Rolling (Desc. Corta) | Código de cliente | Producto | Marca | Clase | Forma de Venta | Código de Tipo de Oferta | Tipo de Oferta | Tipo de Medio de Venta | Cantidad 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) |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | CY CABLE GET CONNECTED | CYZONE | HOGAR | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 9.71 | 12.99 | 1 | 1 | 2.6 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | CY GOLDEN STAR SET ANI | CYZONE | BIJOUTERIE | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 9.1 | 12.99 | 1 | 1 | 3.25 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | CZ CYSOLUX MASC PUN NEG 90G FM | CYZONE | TRATAMIENTO FACIAL | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 3.85 | 5.5 | 1 | 1 | 1.38 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | EXPOFERTAS PA 2018 C-07 | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | FLYER DEVOS MAGNETIC C07 PA | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB BASE CONCENTRE MEDIUM 6 | LBEL | MAQUILLAJE | CATALOGO | 029 | DEMO PROD LINEA - CONCURSO / FESTIVAL | REVISTA | 1 | 4549 | 17.75 | 18.99 | 1 | 1 | 14 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB EFP SOMB KIT CEJAS 4.4 G FM | LBEL | MAQUILLAJE | CATALOGO | 212 | PRODUCTO EN SET - GRAN VENTA ONLINE | MV WEB | 1 | 4549 | 48.6 | 52 | 5 | 5 | 27.95 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB ESS DEMQ CC 125 ML | LBEL | TRATAMIENTO FACIAL | CATALOGO | 030 | PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA | REVISTA | 1 | 4549 | 0 | 0 | 1 | 1 | 17.99 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB ESS DEMQ CC 125 ML | LBEL | TRATAMIENTO FACIAL | CATALOGO | 048 | DEMO PROD LINEA - NIVELES | REVISTA | 1 | 4549 | 28.02 | 29.97 | 3 | 3 | 24 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB NOCT SERUM CC TS 5ML | LBEL | TRATAMIENTO FACIAL | AGREGADO I | 077 | MUESTRAS PARA REGALO - PEDIDOS | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | LB PALETA LL VELOUR BARRA 6.8G | LBEL | MAQUILLAJE | AGREGADO I | 126 | VENTA MUESTRAS - PORTAL VIRTUAL | OTROS | 1 | 4549 | 3.73 | 3.99 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3108 | F | Constantes 3 | 000000752 | SOBRE DE CAMPANA PA 2018 C-08 | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CATALOGO CYZONE PA 2018 C-08 | CYZONE | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CATALOGO LBEL PA 2018 C-08 | LBEL | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CY BOLSO AGATHA | CYZONE | COMPLEMENTOS | CATALOGO | 115 | DEMO NUEVO y CIRCULOS DE CALIDAD INDIVIDUAL | REVISTA | 1 | 4549 | 23.36 | 24.99 | 1 | 1 | 10 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CY ESM KHALI FIN BLING EMERALD | CYZONE | MAQUILLAJE | CATALOGO | 013 | OFERTA MAXIMA | CATALOGO | 1 | 4549 | 3.15 | 4.5 | 1 | 1 | 1.13 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CY LL MAD 4 RED PSYCHO | CYZONE | MAQUILLAJE | CATALOGO | 114 | PROMOCIÓN PROPIA BLOQUE COMISIONABLE FIJA | CATALOGO | 1 | 4549 | 2.43 | 3.25 | 1 | 1 | 0.65 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CZ CYSOLUX MASC PUN NEG 90G FM | CYZONE | TRATAMIENTO FACIAL | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 3.85 | 5.5 | 1 | 1 | 1.38 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CZ DEL BROWN 4 EVER PI | CYZONE | MAQUILLAJE | CATALOGO | 013 | OFERTA MAXIMA | CATALOGO | 1 | 4549 | 3.5 | 4.99 | 1 | 1 | 1.25 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CZ DEL SOFT MORA | CYZONE | MAQUILLAJE | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 3.15 | 4.5 | 1 | 1 | 1.13 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | CZ FLIRTY SEXY PROB C/C 4.5 ML | CYZONE | FRAGANCIAS | AGREGADO III | 082 | MUESTRAS EN LIQUIDACIÓN | OTROS | 1 | 4549 | 0 | 0 | 2 | 2 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | EXPOFERTAS PA 2018 C-07 | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | FLYER DEVOS MAGNETIC C07 PA | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB COLLAGENESSE INTENSIVO 15ML | LBEL | TRATAMIENTO FACIAL | AGREGADO III | 093 | REGALOS ESPECIALES | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB DEL COLOR INTENSE NOIR | LBEL | MAQUILLAJE | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 5.6 | 7.99 | 1 | 1 | 2 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB DESOLBEL DES DRY 48 50 ML | LBEL | CUIDADO PERSONAL | CATALOGO | 015 | OFERTA SUPER ESPECIAL | CATALOGO | 1 | 4549 | 3.85 | 5.5 | 1 | 1 | 1.38 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB LL EFP HIDR CC MALVE | LBEL | MAQUILLAJE | LIQUIDACIONES | 021 | OFERTA ADICIONAL | CATALOGO | 1 | 4549 | 4.48 | 5.99 | 1 | 1 | 1.2 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB MATISSE CR SENSU PT 400 ML | LBEL | TRATAMIENTO CORPORAL | CATALOGO | 030 | PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA | REVISTA | 1 | 4549 | 0 | 0 | 1 | 1 | 18.99 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB MITHROSE PARF 50 ML | LBEL | FRAGANCIAS | CATALOGO | 001 | DEMO NUEVO y CIRCULOS DE CALIDAD | REVISTA | 1 | 4549 | 37.36 | 39.98 | 2 | 2 | 25.02 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB MITHROSE PARF MINI 10 ML | LBEL | FRAGANCIAS | CATALOGO | 030 | PROD PROPIOS de REGALO POR COMPRA UU EN REVISTA | REVISTA | 1 | 4549 | 0 | 0 | 1 | 1 | 10.5 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB NOCT SERUM CC TS 5ML | LBEL | TRATAMIENTO FACIAL | AGREGADO I | 077 | MUESTRAS PARA REGALO - PEDIDOS | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | LB REVE SENS PARF 50ML | LBEL | FRAGANCIAS | CATALOGO | 202 | OFERTA INDIVIDUAL - OFERTA PARA TI | MV WEB | 1 | 4549 | 21.96 | 23.5 | 1 | 1 | 16.49 |
201806 | Panamá | REGION 31 PANAMA | 3120 | G | Constantes 2 | 000000590 | SOBRE DE CAMPANA PA 2018 C-08 | GENERICA | MATERIAL APOYO MKT | AGREGADO III | 057 | MATERIAL IMPRESO PARA OBSEQUIO | OTROS | 1 | 4549 | 0 | 0 | 1 | 1 | 0 |
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
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
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 |
---|---|
112 | |
97 | |
80 | |
69 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |