Card showing the minimum value for a given slicer

[Anonymous]

Hi,

I have two datasets:

1. Shows the products and their prices, for instance:

Product code  Price

111                  20

111                  22

111                  23

222                  120

...

2. Shows countries and average price of the product in this country

Product code   Country  Average price

111                   Poland    20

111                   Italy        25

111                   China      16

I would like to visualise:

a) the country with the minimum price for a chosed product (for instance, a card saying "You should buy this product in China", if the code chosen in the slicer is 111)

b) the total savings if the "best" country is chosen ("You paid xxx, if you bought this in China, you would have paid yyy")

This seems like it should be pretty straightforward however I am stuck at the moment and would appreciate any tips.

Thank you in advance.

[mwegener · Accepted solution]

Hi @Anonymous 

try this Measures

Where to buy = "You should buy this product in " & SELECTCOLUMNS(TOPN(1;'Product by Country';'Product by Country'[Average price];ASC);"Country";'Product by Country'[Country])

Saving =

var top1 = TOPN(1;'Product by Country';'Product by Country'[Average price];ASC)

return "You paid "& AVERAGE('Product'[Price]) &", if you bought this in "&SELECTCOLUMNS(top1;"Country";'Product by Country'[Country])&", you would have paid " & SELECTCOLUMNS(top1;"Saving";'Product by Country'[Average price])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener · Accepted solution]

Hi @Anonymous 

try this Measures

Where to buy = "You should buy this product in " & SELECTCOLUMNS(TOPN(1;'Product by Country';'Product by Country'[Average price];ASC);"Country";'Product by Country'[Country])

Saving =

var top1 = TOPN(1;'Product by Country';'Product by Country'[Average price];ASC)

return "You paid "& AVERAGE('Product'[Price]) &", if you bought this in "&SELECTCOLUMNS(top1;"Country";'Product by Country'[Country])&", you would have paid " & SELECTCOLUMNS(top1;"Saving";'Product by Country'[Average price])

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[mwegener · Accepted solution]

Hi @Anonymous ,

did you solve your problem?

If I answered your question, please mark my post as solution, this will also help others.

Please give Kudos for support.

[Anonymous]

Works great, thank you!