Forum Discussion

v-yulgu-msft's avatar
v-yulgu-msft
Microsoft Employee
9 years ago

Hi Anonymous,

 

What is the relationship between Ventas IMS[Corporacion] and Zona-Gestión[Zona-Gestión]? You said the rank number for Roemmers Corp should be 2 in the right table, but I notices that the data records in right table are not the same as data records in left table.

 

Please try this measure, add it to the right table visual.

Ranking Measure = 
RANKX(
	ALL(
		'Ventas IMS'[IdPais]
	),
		CALCULATE(
		SUM(
			'Ventas IMS'[ImporteVendidoUSD]
		),
		ALLEXCEPT('Zona-Gestión',
			'Zona-Gestión'[IdZonaGestion],'Zona-Gestión'[Zona-Gestión]
		)
))

Regards,
Yuliana Gu

Anonymous's avatar
Anonymous
Not applicable
9 years ago

Hi v-yulgu-msft. Many thanks for your help so far.

 

The table 'Zona-Gestión' is just a master table of that dimension. The idea is that all the reports that I wish to display by 'IdZonaGestion' or 'ZonaGestion', will be displayed by the members of that table.

 

That's why I have an 'IdZonaGestion' in 'Ventas IMS' and same for 'Ventas Mensuales', both fact tables.

 

In this case, there is no direct relationship between Ventas IMS[Corporacion] and Zona-Gestión[Zona-Gestión].

 

I'm sorry to say that the measure that you passed me did not work.

 

The best that I was able to do, was this:

 

As you can see in this example, I used Venezuela as an example. The 'IdPais' VEN has multiple 'IdZonaGestion', in this case 2. The 'IdZonaGestion' VEN-KLI is my company and VEN-OTR is the rest of the market. The measure must rank all the 'Corporacion' from same country. In the example, the rank starts over in every 'IdZonaGestion', but I need it to be by 'IdPais' so the rank is by the whole market. 

 

See this next pic:

 

If I remove 'IdZonaGestion' from the table, I get the real position of the 'Corporacion' that is from VEN-KLI. That position is the one that I need to display in a table by 'Zona-Gestión'. I need the position of the 'Corporacion' from my company but ranked in all the market, including the rest of the market.

 

Thanks so far.