Dynamic ranking

[Anonymous]

Hi everyone!

I'm having a hard time trying to make a mesaure that calculates a dynamic ranking. I need it to respond to all the filters that I have in the report.

The scenario is pretty much like this:

In my model I have a table with sales by IdPais, IdZonaGestion, Corporacion, Fecha (Date). In the report that I'm making I need to display sales only for specific IdZonaGestion, not all of them. But, in the rank must be made considiring the sales of IdZonaGestion that are not shown in the report.

In this fact table I have sales of my company and the market. My company/IdZonaGestion (example: VEN-KLI) has one specific Corporacion (example: Rommers Corp.), and all other Corporacion from same zone (VEN) go to a generic IdZonaGestion (example: VEN-OTR).

So, my rank must rank all sales from same zone (VEN) so I can get where I am positioned in whole market. Lets say  Corporacion "Roemmers Corp." is positioned 2nd. This rank number must be shown in a table visual opened by IdZonaGestion that shows only the IdZonaGestion from my company, that is to say, all the IdZonaGestion that do not finish with "-OTR".

So far, my rank measure looks like this:

Ranking = 
VAR Zona = DISTINCT('Zona-Gestión'[Zona])
RETURN
IF(
	HASONEVALUE('Zona-Gestión'[IdZonaGestion]
	);
	IF(
		SEARCH(
			"-OTR";
			VALUES(
				'Zona-Gestión'[IdZonaGestion]
			);;
			BLANK()
		) = BLANK();
		CALCULATE(
			RANKX(
				ALL(
					'Ventas IMS'[Corporacion]
				);
				'Ventas IMS'[Ventas USD IMS Todas]
			);
			FILTER(
				ALLSELECTED(
					'Ventas IMS'
				);
				LEFT(
					'Ventas IMS'[IdZonaGestion];
					3
				) = Zona
			);
			ALL(
				'Canal'[IdCanal]
			)
		)
	)
)

It is not working as I want. Let me show so next pic:

As you can see, I perform with success a rank measure to rank all Corporacion. What I need, is to show that 2nd position marked in the table that is opened by ZonaGestion.

Any ideas on how can I perform this?

Thanks in advance.

[v-yulgu-msft]

Hi @Anonymous,

According to your description, you want the Ranking value in the last row which is highlighted in red in the rightest table to show as 2, right?

As it's not very clear about source table structure, I was not able to replicate your scenario. Was the above Ranking measure the [Ranking] value you added in the middle table and right table? If so, please try to create a new ranking measure (just modify the Rankx part) and add this new one into the right table visual.

RANKX(
	ALL(
	'Ventas IMS'
	);
	'Ventas IMS'[Ventas USD IMS Todas]
	);

I noticed that there are three tables in your scenario 'Zona-Gestión',  'Ventas IMS' and 'Canal'. Which table were those columns from that added to visuals as shown in your screenshot? Was there any filter or slicer in your report? Would you please simplify your sample data, and share us all the necessary table structure?

Regards,
Yuliana Gu

[Anonymous]

Hi @v-yulgu-msft. Thanks for your reply and help.

You can have a sample pbix in: https://1drv.ms/u/s!AgDdItVNA18ZgW5DBYhFJ5rgVyJW

Yes, I need to be able to link that "2" position of the rank to its respective "IdZonaGestion".

In fact table "Ventas IMS", I have sales of my company and the market. My company/IdZonaGestion (example: VEN-KLI) has one specific Corporacion (example: Rommers Corp.), and all other Corporacion from same zone (VEN) go to a generic IdZonaGestion (example: VEN-OTR).

So, my rank must rank all sales from same zone (VEN) so I can get where I am positioned in whole market. Lets say  Corporacion "Roemmers Corp." is positioned 2nd. This rank number must be shown in a table visual opened by IdZonaGestion that shows only the IdZonaGestion from my company, that is to say, all the IdZonaGestion that do not finish with "-OTR".

[v-yulgu-msft]

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]

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.

[Vvelarde]

@Anonymous

Hi,please share the link to the PBIX.

Regards

Victor

[Anonymous]

Hi @Vvelarde

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

Regards.