Hello people! I'm needing a bit of help. I'm trying to make a mesaure that groups sales by the first 3 letters of a description. In my fact table I have sales open by IdZonaGestion (examples: "ARG-OTR", "ARG-ROE", etc). The sum of all sales of a specific period represents market sales, while the sales of same period but only of those IdZonaGestion wich last 3 letters are not "OTR" represents my sales. So, in my scenario, I need to perform a report thats opened by IdZonaGestion showing only my sales (all the IdZonaGestion that not finish in "OTR") and with a column that has a mesaure that displays the participation of my sales in the market. The sales of market are by country, so, in these case the SUM of sales of the IdZonaGestion that starts with "ARG" (Argentina) are the market sakles, and the SUM of sales of the IdZonaGestion that do not finish in "OTR" are my sales. What I have done so far, is this: And some data sample:IDIdPeriodoIdAnioIdMesIdPaisPresentacionCorporacionLaboratorioIdCompaniaIdClaseTerapeutica1IdClaseTerapeutica2IdClaseTerapeutica3IdClaseTerapeutica4MercadoMercadoPorTipoIdMarcaMarcaSubFamiliaAnioLanzamientoConcentracionFechaLanzamientoMATUnidadesSalidasImporteVendidoImporteVendidoUSDIdPaisIMSGrupoIdZonaGestion120120220122ARG 3-tc Tabl Recubie 150 Mg X 60Glaxosmithkli.corpGlaxosmithkline PhARG-OTRJJ05J05CJ05C1EticoMarca10523-tcLamivudine1996150 MG[Legacy Products]93885,29992894,24063ArgentinaGLAXOSMITHKLI CORPARG-OTR220120220122ARG 5 Asa Sobres 2 G X 30DominguezDominguezARG-OTRAA07A07EA07E1EticoMarca10535 AsaMesalazine20162 G[Legacy Products]000ArgentinaDOMINGUEZARG-OTR320120220122ARG 8-horas Tabl Recubie 1 Mg X 30Elea CorpEleaARG-OTRNN05N05BN05B1EticoMarca93318-horasEszopiclone20071 MG[Legacy Products]44812902,40392969,61728ArgentinaELEA CORPARG-OTR420120220122ARG A Sun Fluide 50+ Envase 40 Ml X 1DispolabDispolabARG-OTRDD02D02AD02A0PopularMarca17041A Sun Fluide 50+Ectoin + Titanium + Vitamin E2013[N/A]Launched in MAT Ene.-14000ArgentinaDISPOLABARG-OTR520120220122ARG A-acido Emoliente Crema 25 Mg 30 G X 1DominguezDominguezARG-OTRDD05D05XD05X0EticoMarca1058A-acido EmolienteTretinoin199325 MG[Legacy Products]312219,28941510,79165ArgentinaDOMINGUEZARG-OTR620120220122ARG A-derma Exomega Leche 400 Ml X 1Pierre FabrePierre Fabre DermoARG-OTRDD02D02AD02A0PopularMarca37981A-derma ExomegaAvena Sativa + Butyrospermum Parkii + Glycerol2009[N/A][Legacy Products]50138592,01968882,33922ArgentinaPIERRE FABREARG-OTR720120220122ARG A-vitel Pant Solar Crema Fps36 120 G X 1MedipharmaMedipharmaARG-OTRDD02D02AD02A0PopularMarca14293A-vitel Pant SolarRetinol2003[N/A][Legacy Products]123,87999655,49622ArgentinaMEDIPHARMAARG-OTR What I guess, is that I need to group the sales in measure "Ventas USD IMS Mercado", so all the IdZonaGestion that start with same first 3 letters have same value. This, with the porpuse of making later a mesaure for the participation. Resuming, I imagine that in the table that I share, I would have "IdZonaGestion", "Ventas USD IMS Propio" (my sales), and "Ventas USD IMS Mercado" (sum of market sales, including me), seeing only the "IdZonaGestion" that are mine (<> from "OTR") but with the total sales of market in last column ("Ventas USD IMS Mercado"). Hope I'm clear and somebody can help me with this. Thanks in advance.

Anonymous

Not applicable

9 years ago

Solved

Measure that groups sales

Hello people!

I'm needing a bit of help.

I'm trying to make a mesaure that groups sales by the first 3 letters of a description.

In my fact table I have sales open by IdZonaGestion (examples: "ARG-OTR", "ARG-ROE", etc). The sum of all sales of a specific period represents market sales, while the sales of same period but only of those IdZonaGestion wich last 3 letters are not "OTR" represents my sales.

So, in my scenario, I need to perform a report thats opened by IdZonaGestion showing only my sales (all the IdZonaGestion that not finish in "OTR") and with a column that has a mesaure that displays the participation of my sales in the market.

The sales of market are by country, so, in these case the SUM of sales of the IdZonaGestion that starts with "ARG" (Argentina) are the market sakles, and the SUM of sales of the IdZonaGestion that do not finish in "OTR" are my sales.

What I have done so far, is this:

And some data sample:

IdPeriodo

IdAnio

IdMes

IdPais

Presentacion

Corporacion

Laboratorio

IdCompania

IdClaseTerapeutica1

IdClaseTerapeutica2

IdClaseTerapeutica3

IdClaseTerapeutica4

Mercado

MercadoPorTipo

IdMarca

Marca

SubFamilia

AnioLanzamiento

Concentracion

FechaLanzamientoMAT

UnidadesSalidas

ImporteVendido

ImporteVendidoUSD

IdPaisIMS

Grupo

IdZonaGestion

201202

2012

ARG

3-tc Tabl Recubie 150 Mg X 60

Glaxosmithkli.corp

Glaxosmithkline Ph

ARG-OTR

J05

J05C

J05C1

Etico

Marca

1052

3-tc

Lamivudine

1996

150 MG

[Legacy Products]

3885,29992

894,24063

Argentina

GLAXOSMITHKLI CORP

ARG-OTR

201202

2012

ARG

5 Asa Sobres 2 G X 30

Dominguez

ARG-OTR

A07

A07E

A07E1

Etico

Marca

1053

5 Asa

Mesalazine

2016

2 G

[Legacy Products]

Argentina

DOMINGUEZ

ARG-OTR

201202

2012

ARG

8-horas Tabl Recubie 1 Mg X 30

Elea Corp

Elea

ARG-OTR

N05

N05B

N05B1

Etico

Marca

9331

8-horas

Eszopiclone

2007

1 MG

[Legacy Products]

448

12902,4039

2969,61728

Argentina

ELEA CORP

ARG-OTR

201202

2012

ARG

A Sun Fluide 50+ Envase 40 Ml X 1

Dispolab

ARG-OTR

D02

D02A

D02A0

Popular

Marca

17041

A Sun Fluide 50+

Ectoin + Titanium + Vitamin E

2013

[N/A]

Launched in MAT Ene.-14

Argentina

DISPOLAB

ARG-OTR

201202

2012

ARG

A-acido Emoliente Crema 25 Mg 30 G X 1

Dominguez

ARG-OTR

D05

D05X

D05X0

Etico

Marca

1058

A-acido Emoliente

Tretinoin

1993

25 MG

[Legacy Products]

2219,28941

510,79165

Argentina

DOMINGUEZ

ARG-OTR

201202

2012

ARG

A-derma Exomega Leche 400 Ml X 1

Pierre Fabre

Pierre Fabre Dermo

ARG-OTR

D02

D02A

D02A0

Popular

Marca

37981

A-derma Exomega

Avena Sativa + Butyrospermum Parkii + Glycerol

2009

[N/A]

[Legacy Products]

501

38592,0196

8882,33922

Argentina

PIERRE FABRE

ARG-OTR

201202

2012

ARG

A-vitel Pant Solar Crema Fps36 120 G X 1

Medipharma

ARG-OTR

D02

D02A

D02A0

Popular

Marca

14293

A-vitel Pant Solar

Retinol

2003

[N/A]

[Legacy Products]

23,8799965

5,49622

Argentina

MEDIPHARMA

ARG-OTR

What I guess, is that I need to group the sales in measure "Ventas USD IMS Mercado", so all the IdZonaGestion that start with same first 3 letters have same value. This, with the porpuse of making later a mesaure for the participation.

Resuming, I imagine that in the table that I share, I would have "IdZonaGestion", "Ventas USD IMS Propio" (my sales), and "Ventas USD IMS Mercado" (sum of market sales, including me), seeing only the "IdZonaGestion" that are mine (<> from "OTR") but with the total sales of market in last column ("Ventas USD IMS Mercado").

Hope I'm clear and somebody can help me with this.

Thanks in advance.

Anonymous

Check the PBIX.

https://drive.google.com/file/d/0B95C8CKdTZE3ZVdvY2lNR2YyczA/view?usp=sharing

ImporteVendidoTotal =
VAR PAIS =
    DISTINCT ( 'Zona-Gestión'[Pais] )
RETURN
    IF (
        HASONEVALUE ( 'Zona-Gestión'[IdZonaGestion] ),
        IF (
            SEARCH ( "-OTR", VALUES ( 'Zona-Gestión'[IdZonaGestion] ),, BLANK () )
                = BLANK (),
            CALCULATE (
                SUM ( 'Ventas IMS'[ImporteVendidoUSD] ),
                FILTER ( ALLSELECTED ( 'Ventas IMS' ), 'Ventas IMS'[IdPaisIMS] = PAIS ),
                ALLEXCEPT ( 'Ventas IMS', 'Ventas IMS'[IdAnio], 'Ventas IMS'[IdMes] )
            )
        ),
        SUM ( 'Ventas IMS'[ImporteVendidoUSD] )
    )

Regards

Victor

Lima - Peru

	Ventas USD IMS Propio	Ventas USD IMS Mercado	%
ARG-POE	5537,48	638619,97	0,86710098
ARG-RAY	2533,67	638619,97	0,39674143

Forum Discussion

Measure that groups sales

11 Replies

Related content

Grouping Measures into categories

Fabric & Azure Data User Group Program Requirements & Benefits

Community User Group Experience

Measure for total sales by sales group

Customer Sales Grouping Report