Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
mazingazeta
New Member

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

 

1 ACCEPTED SOLUTION
camargos88
Community Champion
Community Champion

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

 

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



View solution in original post

2 REPLIES 2
camargos88
Community Champion
Community Champion

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

 

 

Capture.PNG



Did I answer your question? Mark my post as a solution!

Proud to be a Super User!



AlB
Super User
Super User

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 

SU18_powerbi_badge

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors