Forum Discussion
Help with DAX Measure (LOOKUPVALUE, TOPN, RANKX; MAX, Groupby)
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
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())
2 Replies
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
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())
