Hello, I was trying to show the top 1 Company Country in Spend per Category for each Category. Therefor I've created an insights listing the attributes: Category, Country and metric: Spend. I'm filtering Top 1 out of Company Country based on Spend (Group Ccy) It's not showing what I intended: it's showing the Country with the biggest spend for all categories where that country has spend. I'm not sure what I should change here. Can you help?

Hi Hans, In this case, it would be necessary to create a custom metric that uses the TOP filter along with the WITHIN clause, in order to lock the calculation by Category only. The metric would look something like this:

SELECT (Spend) WHERE TOP(1) IN (SELECT Spend BY Country) WITHIN (Category, ALL OTHER)

The bit which makes the difference here is the

WITHIN (Category, ALL OTHER)

, which will ensure that it makes the calculation using the values for each category. The TOP filter you had added on the Analytical Designer doesn’t have that extra clause, by design. Then you can add this metric to your insight and slice it by Category and Country like you did before. It should display the results you are looking for! 🙂

🎉 New note created.

Thanks Francisco, I will try that out!