Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
2 years ago
Solved

Select Values with max Values in the subtotal

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 depart...
  • talespin's avatar
    talespin
    2 years ago

    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
            )
        )

     

Anonymous's avatar
Anonymous
Not applicable
2 years ago

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.