Here, we will learn how to create a calculation to retrieve the top sales product value over each category and the corresponding name of the top sale product. The value is Sales Amount.

We will start by adding the visualization, Table chart with dimension Category, and master measure Sales $.

Follow these steps to create the visualizations:

1. Click on the Edit button to enter the edit mode.
2. Click on the Charts button on the asset panel, which is on the left-hand side of the screen, and find the Table chart.
3. Click on the Table chart, and then drag and drop it into the empty space at the top of the sheet:

4. Click on the Add dimension button and select Category in the Dimensions section.
5. Click on the Data heading in the properties panel to expand it.
6. Find the Add column button, click on it, and choose Measure.
7. Select Sales $ in the Measure section.
8. Fix the measure Number formatting to show a number with two decimal places.
9. Click on the Add column button again to add the second measure.
10. Click on the fx button to open the expression editor, and then type the expression for the new measure, max(aggr(sum(SalesAmount), CategoryName, ProductName)).
11. Click on the Apply button to close the expression editor and save the expression.
12. In the Label of the measure, type Top Sales Product Value.
13. Fix the measure Number formatting to show a number with two decimal places.
14. Click on the Done button to enter the visualization mode. The Table chart will look like this:

15. Click on the Edit button to enter the edit mode.
16. Go to the properties panel and click on the Data heading to expand it.
17. Click on the Add column button again to add the third measure.
18. Click on the fx button to open the expression editor, and then type the expression for the new measure, Firstsortedvalue(ProductName, -aggr(Sum(SalesAmount), CategoryName,ProductName)).

19. Click on the Apply button to close the expression editor and save the expression.
20. In the Label of the measure, type Top Sales Product Name.
21. Find the Totals function property in the measure and select None to not display a value in the total heading. The Table chart will look like this:

We can now use the AGGR function to retrieve information about the top-performing product in each category on the sales amount.

The AGGR function works like the script load with a group by creating a virtual table with one measure (first parameter) and one or more dimensions as the second to nth parameter. In this example, the aggregation dimension is Category and Product.

By combining Max() and AGGR(), we found the highest value for the Sales Amount of a product in each category they belong to.

We used FirstSortedValue(value, sorting_argument) to show the corresponding product with the highest value for the Sales Amount. This returns the value (product) that corresponds to the result of the sorting argument. In general, the sorting argument is a field, but we can also use an AGGR() expression. We add a minus sign in front of the sorting argument to start the sorting from highest to lowest, and not from lowest to highest, which is the default sorting criteria.