Select Values with max Values in the subtotal

To achieve this in Power BI or Excel, you can use a combination of functions like RELATED, CALCULATE, MAXX, and EARLIER. Here's a general guide on how you could approach this:

Let's assume you have a table named MasterData with columns CustomerName and Department, and another table named RevenueData with columns CustomerName, Revenue, SalesAgent, and Region.

1. Create relationships:

   • Make sure there is a relationship between the CustomerName column in the MasterData table and the CustomerName column in the RevenueData table.

2. Create calculated columns in the MasterData table:

   • Most productive sales agent:

MostProductiveAgent =
CALCULATE(
TOPN(1, RevenueData, RevenueData[Revenue], DESC),
FILTER(RevenueData, RevenueData[CustomerName] = MasterData[CustomerName])
) [SalesAgent]

Main region:

MainRegion =
CALCULATE(
TOPN(1, RevenueData, RevenueData[Revenue], DESC),
FILTER(RevenueData, RevenueData[CustomerName] = MasterData[CustomerName])
) [Region]

1. Adjust the column names based on your actual column names.

2. Now, you should have two new columns in your MasterData table (MostProductiveAgent and MainRegion) that display the most productive sales agent and main region based on revenue and department.

Please note that this approach assumes there is a one-to-many relationship between the MasterData and RevenueData tables. If there are different relationships or if you need to consider additional conditions, you may need to modify the formulas accordingly. Additionally, make sure to handle potential scenarios where there might be ties for the highest revenue.

Remember to adapt the column names and table names based on your actual data model.

This solution doesn't works, but, most importatnt, does not consider that there is a "double relation". 

MostProductiveAgent is not only linked to the Customer but also to the department. 
As visible in the example, the MostProductiveAgent  is "C" but i have to not consider him because the department is not in scope.

Hi Anonymous ,

You can try below calculated column to create 'sales agent' and 'region' column in Master Data.

MostProductiveSalesAgent = 
VAR filter_table =
    FILTER (
        'Revenue',
        'Revenue'[customer] = MAX ( 'Master Data'[customer] )
            && 'Revenue'[department] = MAX ( 'Master Data'[department] )
    )
RETURN
    CALCULATE (
        VALUES ( 'Revenue'[sales agent] ),
        filter_table,
        TOPN ( 1, filter_table, [revenue], DESC )
    )

MainRegion = 
VAR filter_table =
    FILTER (
        'Revenue',
        'Revenue'[customer] = MAX ( 'Master Data'[customer] )
            && 'Revenue'[department] = MAX ( 'Master Data'[department] )
    )
RETURN
    CALCULATE (
        VALUES ( 'Revenue'[region] ),
        filter_table,
        TOPN ( 1, filter_table, [revenue], DESC )
    )

Best Regards,
Adamk Kong

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly