Help with DAX Measure (LOOKUPVALUE, TOPN, RANKX; MAX, Groupby)

[mazingazeta]

Hello, I have the following question: I need the NAME of the Agent with the most amount per Customer. (the result should be : a - Hannes, b - fritz, c - mike). I tried and thinked a lot of different measures (TOPN,Lookup, MAX, Rankx) ...but didnt get it right, anyone an idea?

Customer - Agent - Amount

a               -  fritz   -  1o

a                - fritz - 1

a                - hannes - 20

a                - hannes 3

b               - fritz    - 5

b               -  mike - 3

b                - hannes -1

c                - mike - 4

c                 - fritz - 1

[camargos88 · Accepted solution]

@mazingazeta ,

You can use these measures:

_TotalAgent = SUMX(SUMMARIZE('Table', 'Table'[Agent], "Total", SUM('Table'[Amount])), [Total])

_Rank = IF(HASONEVALUE('Table'[Agent]), RANKX(ALL('Table'[Agent]), [_TotalAgent],,DESC), BLANK())

[AlB]

Hi  @mazingazeta 

1. Place Customer in a table visual

2. Create this measure and place it in the visual

Measure =
CONCATENATEX (
    TOPN (
        1,
        Table1,
        CALCULATE ( SUM ( Table1[Amount] ), ALLEXCEPT ( Table1, Table1[Agent] ) ), DESC
    ),
    Table1[Agent],
    ", "
)

Please mark the question solved when done and consider giving a thumbs up if posts are helpful.

Contact me privately for support with any larger-scale BI needs, tutoring, etc.

Cheers