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 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.
Solved! Go to 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
Hi, What option do you want to see? or a different ?
Regards
Victor
Lima - Peru
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.
@Anonymous
Hi, Please check the PBIX.
https://drive.google.com/file/d/0B95C8CKdTZE3MjYtRG8zckp6Nkk/view?usp=sharing
Regards
Victor
Lima - Peru
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?
@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
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.
@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
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!
@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
@Vvelarde, thank you so much for your help!
That whas awesome! Now I have everything working.
Greetings from Montevideo, Uruguay.
Have a nice day!
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.
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 |
---|---|
109 | |
98 | |
77 | |
66 | |
54 |
User | Count |
---|---|
144 | |
104 | |
100 | |
86 | |
64 |