Select Values with max Values in the subtotal

[Anonymous]

Hi all, 

I hava this issue that I'am not able to solve with combinations of formulas lookupvalue, related or something similar.

I have a master data table with the customer name and the department.

The revenue are in another table, linked to the firs one. 
I have to add some columns in the master data file, with the information of the most productive sales agent, main region, etc. based on the revenue (data) and department (assigned).

thank for help!

[talespin · Accepted solution]

Hello,

Thank you Adamk Kong.

It is better to do it in Power Query.

The solution I am sharing is for Calculated column(Not Measure)

Since you did not share any file, datamodel, I am considering these tables as not related.

Please see if this helps.

SalesAgent =

VAR _customer = MasterData2[Customer]

VAR _Dept = MasterData2[Department]

VAR _RevenueByAgent =

    ADDCOLUMNS (

        SUMMARIZE (

            Revenue2,

            Revenue2[Customer],

            Revenue2[Department],

            Revenue2[Sales_Agent]

        ),

        "RevenueBySalesAgent",            

                CALCULATE (

                    SUM ( Revenue2[Revenue] ),

                    Revenue2[Customer] = _customer

                        && Revenue2[Department] = _Dept

                )

    )

RETURN

    CALCULATE (

        VALUES ( Revenue2[Sales_Agent] ),

        TOPN (

            1,

            FILTER (

                _RevenueByAgent,

                Revenue2[Customer] = _customer

                    && Revenue2[Department] = _Dept

            ),

            [RevenueBySalesAgent], DESC

        )

    )

[v-kongfanf-msft]

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

[Anonymous]

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.

[123abc]

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.