Forum Discussion
Calculate Max value in a table visual
Hi Everyone,
I am trying to write a DAX measure to calculate Max value in a table visual. Please refer to the image below:
I created a 3-column table visual, column 1 and 3 are dragged from 'AD' table, column 3 [% on Site] is a measure.
Column 2 [Date] is dragged from 'All Entries' table.
I try to calculate Max % on site of each Branch. The result should look like this:
USS 54.55%
CFM 27.58%
DMA 27.27%
...
Could someone help me with the DAX expression?
Thank you,
Vikki
4 Replies
Vikki , Try a new measure like
var _max = calculate(MaxX(summarize(Table, Table[Branch], Table[site], "_1",[Max %]), [_1]), filter(allselected(Table), Table[Branch] = max(Table[Branch]) && Table[site] = max(Table[site])))
return
if( [Max %] = _max, [Max %], blank())Hi Vikki ,
Is there any relationship created between table AD and All Entries? Could you please provide some sample data in table All Entries? From your screenshot, it looks like there are multiple dates for each branch, do you want the final result to get the [% on Site] of the latest date for each branch? For example, Branch: USS only shows Date Sep 17, will the final result be what is circled in red in the table below?
Expected result??
Best Regards
Hi Anonymous amitchandak ,
Thank you for looking into this.
One of the other problem I am facing is there is many to many relationship between AD table and All Entries table, through people's names, which is not a reliable key. But for now, the client didn't provide anything else.
I wonder if this is not acheivable unless I have a unique key and 1 to many relationship between AD and All Entries table.
Thanks anyway,
Vikki
