Measure that groups sales

[Anonymous]

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:

ID

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

1

201202

2012

2

ARG

3-tc Tabl Recubie 150 Mg X 60

Glaxosmithkli.corp

Glaxosmithkline Ph

ARG-OTR

J

J05

J05C

J05C1

Etico

Marca

1052

3-tc

Lamivudine

1996

150 MG

[Legacy Products]

9

3885,29992

894,24063

Argentina

GLAXOSMITHKLI CORP

ARG-OTR

2

201202

2012

2

ARG

5 Asa Sobres 2 G X 30

Dominguez

Dominguez

ARG-OTR

A

A07

A07E

A07E1

Etico

Marca

1053

5 Asa

Mesalazine

2016

2 G

[Legacy Products]

0

0

0

Argentina

DOMINGUEZ

ARG-OTR

3

201202

2012

2

ARG

8-horas Tabl Recubie 1 Mg X 30

Elea Corp

Elea

ARG-OTR

N

N05

N05B

N05B1

Etico

Marca

9331

8-horas

Eszopiclone

2007

1 MG

[Legacy Products]

448

12902,4039

2969,61728

Argentina

ELEA CORP

ARG-OTR

4

201202

2012

2

ARG

A Sun Fluide 50+ Envase 40 Ml X 1

Dispolab

Dispolab

ARG-OTR

D

D02

D02A

D02A0

Popular

Marca

17041

A Sun Fluide 50+

Ectoin + Titanium + Vitamin E

2013

[N/A]

Launched in MAT Ene.-14

0

0

0

Argentina

DISPOLAB

ARG-OTR

5

201202

2012

2

ARG

A-acido Emoliente Crema 25 Mg 30 G X 1

Dominguez

Dominguez

ARG-OTR

D

D05

D05X

D05X0

Etico

Marca

1058

A-acido Emoliente

Tretinoin

1993

25 MG

[Legacy Products]

31

2219,28941

510,79165

Argentina

DOMINGUEZ

ARG-OTR

6

201202

2012

2

ARG

A-derma Exomega Leche 400 Ml X 1

Pierre Fabre

Pierre Fabre Dermo

ARG-OTR

D

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

7

201202

2012

2

ARG

A-vitel Pant Solar Crema Fps36 120 G X 1

Medipharma

Medipharma

ARG-OTR

D

D02

D02A

D02A0

Popular

Marca

14293

A-vitel Pant Solar

Retinol

2003

[N/A]

[Legacy Products]

1

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.

[Vvelarde]

@Anonymous

Hi, What option do you want to see? or a different ?

Regards

Victor

Lima - Peru

[Anonymous]

Hi @Vvelarde, thanks for your reply.

It should be like this:

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

Ventas USD IMS Mercado must sum all sales from "ARG", including "ARG-OTR".

Thanks in advance.

[Vvelarde]

@Anonymous

Hi, Please check the PBIX. 

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

Regards

Victor

Lima - Peru

[Anonymous]

@Vvelarde

This is the result when applying the ALL function:

I get the same value (the grand total) for every IdZonaGestion. But what I really need are something like subtotals, one for each country ("ARG", "BRA", "VEN", etc.)

What should we change in the mesaure to make this possible?

[Vvelarde]

@Anonymous

Hi, The more simple way is 

modify the measure: 

ImporteVendidoTotal = CALCULATE(SUM(Table1[ImporteVendidoUSD]),ALLEXCEPT(Table1,Table1[IdAnio],Table1[IdMes],Table1[IdPaisIMS]))

and add to table / matrix the Column Pais. If you don't want to see reduce his column Width.

Regards

Victor

[Anonymous]

Thanks @Vvelarde for your fast reply.

The problem is that I'm using the Matrix Preview object, that makes drill down in the dimentions added to rows. So, if I add Pais to the Matrix, the only thing that changes is that I have a new dimention analysis.

[Vvelarde]

@Anonymous

Change the measures:

VentasUSDIMSPropio =
IF (
    SEARCH ( "-OTR", VALUES ( Table1[IdZonaGestion] ),, BLANK () ) = BLANK (),
    SUM ( Table1[ImporteVendidoUSD] ),
    BLANK ()
)

ImporteVendidoTotal =
VAR PAIS =
    VALUES ( Table1[IdPaisIMS] )
RETURN
    IF (
        SEARCH ( "-OTR", VALUES ( Table1[IdZonaGestion] ),, BLANK () ) = BLANK (),
        CALCULATE (
            SUM ( Table1[ImporteVendidoUSD] ),
            FILTER ( ALLSELECTED ( Table1 ), Table1[IdPaisIMS] = PAIS ),
            ALLEXCEPT ( Table1, Table1[IdAnio], Table1[IdMes] )
        )
    )

Maybe need some adjusts accord to your model and visuals.

Regards

Victor

Lima - Peru

[Anonymous]

@Vvelarde

Here is the pbix: https://1drv.ms/u/s!AgDdItVNA18ZgW2QrC4tlIuMfZEO

It will probably be better if you can see it.

I left measure "Ventas USD IMS Mercado" with previous formula so it works, even though is not the result that I want.

Thanks!

[Vvelarde · Accepted solution]

@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

[Anonymous]

@Vvelarde, thank you so much for your help!

That whas awesome! Now I have everything working.

Greetings from Montevideo, Uruguay.

Have a nice day!

[Anonymous]

@Vvelarde

This is how the mesaure "Ventas USD IMS Mercado" looks like:

Ventas USD IMS Mercado = 
Var Pais = 
	VALUES('Ventas IMS'[IdPaisIMS])
	
RETURN
	IF(
		SEARCH(
			"-OTR";
			VALUES(
				'Ventas IMS'[IdZonaGestion]
			);;
			BLANK()
		) = BLANK();
		CALCULATE(
			SUM(
				'Ventas IMS'[ImporteVendidoUSD]
			);
			FILTER(
				ALLSELECTED(
					'Ventas IMS'
				);
				'Ventas IMS'[IdPaisIMS] = Pais
			);
			ALLEXCEPT(
				'Ventas IMS';
				'Ventas IMS'[Fecha]
			)
		)
	)

It hives me the next error:

 
MdxScript(Model) (2881, 31) Calculation error in measure 'Ventas IMS'[Ventas USD IMS Mercado]: A table of multiple values was supplied where a single value was expected.