I created a metric where I can see the revenue of a product split by different countries per month. I can then use the aggregator “MAX” to give me the maximum obtained revenue - e.g. in which country the revenue was the highest that month. I would like to use this value (thus the name of this country) in a separate table where I have already other information on this product. What I would like to add in this table is thus “Product X - Top performing country”. Would this be possible?
Thank you very much in advance!
Page 1 / 1
Hi Olivia,
Hope you’re well, thank you for raising this question on the Community.
With modeling there are multiple ways to build things, I hope I’ll be able to explain my logic clearly but there might be other ways.
To return the Top Country based on the Revenue per Month, I’d suggest to use an IF condition that will compare the max revenue by Product and Month to the max revenue by Product, Month and Country, if there is a match, then return the Country.
I’ve built an example:
Here is my data load.
Here is the metric that returns the Max revenue by Product, Month and Country. Hope it looks like what you have.
Finally, here is the comparison + match made to return only the Top Country.
Here is the formula.
if( DataLoad.amount aby max: DataLoad.month, DataLoad.Product] = DataLoad.amount aby max: DataLoad.Country, DataLoad.month, DataLoad.Product] , Country) cremove firstnonblank: Country]
My metric is dimensioned by Month and Product, but that is up to you to use these dimensions, you can do it regardless of them just by removing the useless modifiers with in the rby max: … ].
Hope it helps.
Best, Benoit
Additional note: If two countries are equals, it would only return one result, the firstnonblank item in your Country list. So, for example if Brazil and Japan are even, Brazil would be displayed, assuming your Country dimension is sorted by alphabetical order.
Hello Benoit,
Thank you very much for your answer. This was exactly what I was looking for.
