Forum Discussion
MAX value and corresponding name field in a visualization
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.
6 Replies
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.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.
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 looking for solution... please respond if anyone have idea
