Reference Maximum Text into table visual

[hemann]

Hi, 

I am wanting to create a table visual (as per below) for Top 5 series with measures. I have been successful except for 'most active state' calculation (refer circled). Below is the data used to summarise the table. It takes top 5 series (based on qty), total quantity for that series, most active state quantity, and the most active state (based on quantity). 

I have tried using Maxx to bring most active state without luck. Please help 

Series	State	Volume
A	NSW	21
A	SA	60
A	VIC	7
B	VIC	50
C	NSW	10
D	NSW	7
E	NSW	22
E	QLD	177
F	VIC	19
G	NSW	50
H	NSW	10
I	NSW	19
I	VIC	1950
J	NSW	820
J	VIC	7
K	QLD	11
L	NSW	14
M	SA	8
N	NSW	16
N	VIC	155

[hemann]

Thanks worked a treat

[jdbuchanan71 · Accepted solution]

@hemann 

Give this a try.

Most Active State =
FIRSTNONBLANK (
    TOPN ( 1, VALUES ( 'Table'[State] ), CALCULATE ( SUM ( 'Table'[Volume] ) ) ),
    1
)