MAX value and corresponding name field in a visualization

[wsilva_916]

I want to include a card or something similar in a dashboard to list the highest sales figure (i.e. MAX([sales] ), and also include the name of the related [salesperson].  I have a measure to calculate the max sales number, but I can't see any way to pull in another field from the same row.  Is this possible?  I'm open to using any type of visualization that will accomplish this. 

[GANESH3400]

I Have 24 income generated sources as columns, Man power, date. machine, tools. Now i wants to get row level dynamic maximun1, maximum2, Maximum 3  values with corresponding names. Red highlighted feilds required in power bI. 

NAME	DATE	TOOLS	JOB	SOURCE1	SOURCE2	SOURCE3	SOURCE4	SOURCE5	SOURCE6	SOURCE24	MAX1	MAX1 NAME	MAX2	MAX2 NAME
XAVIOUR	05-09-14	DIE	1	500	300	200	400	755	455	800	800	SOURCE24	755	SOURCE5
ANDRES	04-05-2020	DIE	2	900	450	120	640	100	899	950	950	SOURCE24	900	SOURCE1
SONALI	06-04-2021	MAK	3	1000	120	480	394	1345	1601	010	1601	SOURCE6	1345	SOURCE5
KANIL	26-12-2018	GEL	4	120	130	143	165	155	164	195	195	SOURCE24	164	SOURCE6
RAMSI	21-02-2016	MIN	5	012	1500	1600	172	138	1849	161	1849	SOURCE6	1600	SOURCE3

[GANESH3400]

looking for solution... please respond if anyone have idea

[Anonymous]

Ok yep it can be done.  Here is a slice of code that can do it, and what you might need to tweak:

Max Value Name = var MaxValue = CALCULATE(MAX(TestData[Amount]), all(TestData))
RETURN 
CALCULATE(
    FIRSTNONBLANK(TestData[Name], TestData[Name]),
    TestData[Amount] = MaxValue
)

So this code assumes you don't want to filter your sales table during the visual, if you do replace the "ALL" with "ALLEXCEPT".

The variable at the top defines what the max value is.  The code below says to take the first non-blank in your data table where the amount is the same as your max value.  This will also break any ties when you have 2 records of the same amount that happen to be max.  It will simply give you the first of those should that occur.

[wsilva_916]

Thank you very much for the answer, Ross.  It definitely works, but I'm realizing now I needed to include more detail in my original question.  I'm not actually trying to return a single row from the table.  Rather I want to locate the sales person with the highest aggregated sales over several months. 

So, I have the following fields in the data I'm importing:

[FullName] and [SalesAmount]

And I have created a measure as:

sumSalesAmount = SUM( [SalesAmount] )

Looks like the MAX function won't accept a measure, so I cannot simply replace the column references with a reference to the measure.  I do wish for the name of the sales person and the amount to dynamically adjust to a slicer I'll include in the dashboard for different sales teams.  Also, it's not a hard requirement but it would be very nice to return multiple names in the event of a tie. 

[Anonymous · Accepted solution]

Ok yes you could do this with a Table Visual.  Put in the FullName into the Values box.  You can also include the sales amount if you wish.

Under "Visual Level Filters", click on the FullName field and select the Filter Type "Top N".  Select 1 and place into the By Value box, the sum function.

[wsilva_916]

Perfect! Thank you!