Max Value Path/Drilldown calculation

Question

Sample Dashboard: Finding Max Value Path ←Arena : Click the link to view dashboard / copy underlying analysis.

I have a data table that contains multiple dimension columns and a measure column called “Value.”

My users need to dynamically select a combination of these dimensions to group by and sum the “Value” column. The goal is to find the combination of dimensions that yields the maximum sum of the “Value” column across all possible combinations.

To illustrate this scenario, I have created a dashboard above that manually demonstrates the process for just three dimension combinations. However, this approach becomes impractical as the number of dimensions increases, and a more dynamic solution is required.

Ideally, I would like to explore a dynamic approach to solve this problem, either through creating a new dataset with a different structure.

One potential solution could be to pivot all column values into a single new dimension and then simply select the top 1 result. But this covers a path length of 1 only.

Another approach that comes to mind is to utilize a recursive Common Table Expression (CTE) in an SQL statement. However, this method might limit the ability to calculate based on user-applied filters, which is a desirable feature in this scenario.

The crux of the problem lies in finding an efficient and dynamic solution that can handle varying numbers of dimensions and user-applied filters while still identifying the combination that yields the maximum sum of the “Value” column.

Example : Starting data table:

If i wan to analyse this data, i might create a pivot and slice Value column by one of the available 3 options (Country, Category, Color). From these 3 options i can see which individual slice gave the highest value and make that my starting point.

So here we can get these 3 pivot tables:

By Country:

By Category:

By Color:

Here Yelow with Value of 6,225 is highest among all three options, so i should start with Yellow. Now i need to find what is level 2 filtering should be, so i filter my data by Yelow, and repeat process for Country and Category now

Color --> Category:

Color --> Country:

Now i see for level two i should select PC with max value of 2217 under yellow.

Finally for last slice i add Categoyr under Country and see which has the biggest value. Color --> Category --> Country:

Now i see the recommended path i should suggest is Color --> Category --> Country drill down. Specifically Yellow --> PC --> Japan

This path would have greatest value at each level.