Medida de Texto

[Anonymous]

Hola,

Tengo una tabla A con una variedad de productos de Mi Empresa, y una tabla B que recopila los precios de la competencia para cada producto de mi tabla A (para cada producto hay un total de 4-5 registros de precio diferente x competidor).

En el Desktop creé la siguiente tabla, que me compara por producto, MI precio vs el MIN de la competencia.

La medida del Mín está calculada OK, pero necesito que en la misma tabla me refleje cuál es el competidor asociado a ese Precio Min calculado. En principio lo que hice fue poner el campo "Competidor" de la tabla B en la tabla del desktop, pero por lo que veo al hacer esto NO me trae el nombre del competidor asociado al pr Mín, sino que me refleja el primer competidor que encuentra en la tabla B asociado al producto (no necesariamente es el de menor precio).

Cómo puedo resolver esto?

Hay una medida que me traiga el nombre del competidor cuyo precio es el min??

Gracias!!

[amitchandak]

@Anonymous , Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.

I think Rank should be able to help you, Ascending rank on price can give rank 1 for the lowest price. You may need to create a category or subcategory rank.

For Rank Refer these links
https://radacad.com/how-to-use-rankx-in-dax-part-2-of-3-calculated-measures
https://radacad.com/how-to-use-rankx-in-dax-part-1-of-3-calculated-columns
https://radacad.com/how-to-use-rankx-in-dax-part-3-of-3-the-finale
https://community.powerbi.com/t5/Community-Blog/Dynamic-TopN-made-easy-with-What-If-Parameter/ba-p/367415

[Anonymous]

Hi @amitchandak 

Lets say this is my table A:

Product	My Price
A	100
B	53
C	70

And this is my table B (related with A by "Product")

Product	Competitor	Price
A	COMPETITOR 1	98
A	COMPETITOR 2	103
B	COMPETITOR 1	55
B	COMPETITOR 2	53

The result I want is the following:

Product	My price	Min. Competitors Price	Min. Competitor
A	100	98	COMPETITOR 1
B	53	53	COMPETITOR 2

I have created de "Min. Price" Measure and its OK, but I am having problems with displaying WHICH competitor is the one with the min. price for each product (the last column).

Hope this is more clear.

Thanx

[v-deddai1-msft · Accepted solution]

Hi @Anonymous ,

You can create the following two measure:

Min. Competitors Price = CALCULATE(MIN(tableB[Price]),FILTER(ALL(tableB),tableB[Product] = MAX(tableA[Product])))

Min. Competitor = VAR a = [Min. Competitors Price] return CALCULATE(VALUES(tableB[Competitor]),tableB[Price] = a)

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[Anonymous]

Hi Dedmon,

Me again.

The measure was very useful but now Im having the following issue.

Lets say I have a product C,

My price: 100

Min Competitors price: 90

The thing is there are two different competitors with the price $90 (Competitor 3 and 4)

In this case the measure doesnt work because apparently, it expects only one result.

How can I make it work? 

It doesnt need to show both competitors names, but at least identify one.

Thanks!!

[Anonymous]

Hi Dedmon, @v-deddai1-msft 

Me again.

The measure was very useful but now Im having the following issue.

Lets say I have a product C,

My price: 100

Min Competitors price: 90

The thing is there are two different competitors with the price $90 (Competitor 3 and 4)

In this case the measure doesnt work because apparently, it expects only one result.

How can I make it work? 

It doesnt need to show both competitors names, but at least identify one.

Thanks!!

[amitchandak]

@Anonymous , refer to this file

https://www.dropbox.com/s/fmf042qqv7lu7l6/Competitorprice.pbix?dl=0