Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to Solution.
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())
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())
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
User | Count |
---|---|
41 | |
27 | |
23 | |
19 | |
16 |
User | Count |
---|---|
55 | |
35 | |
21 | |
18 | |
15 |