Return category label and value after summarizing table.

[Wendeley-North]

Hi, 

Would like to get the maximum and minimum of certain labels are summarizing them.

The actual data is simple but tedious to replicate, so I can only give a very brief sample:

Date	Grouping 1	Item ID	Country	Sector	Quantity
29-Jan-99	A	12431232	AB	ZZ	-100
26-Feb-99	A	12312333	AE	ZC	10
29-Jan-99	B	16453453	AC	ZA	100
26-Feb-99	B	13232322	AE	ZF	50

Would like to be able to summarize the table based on the Country (Dates and Grouping 1 are provided by slicers and row context in a table respectively), and return the label for the country respectively. 

Would like to reuse the code to do the same for the sector as well.

Here's a sample code I tried which works for MINX values, but not MAXX due to the presence of blanks in the sector labels:

maxSector = 
    VAR vals = SUMMARIZE('Table', 'Table'[Sector], "sectorQty", SUM(Table[Sector]))
    VAR measureMax = MAXX(vals, [sectorQty])
    VAR maxSectorName = CALCULATE(MAXX(FILTER(vals, [sectorQty] = measureMax), [Sector]))
RETURN
    maxSectorName & " (" & FORMAT(measureMax, "0.00") & ")"

Would like to return the first result for which sector is not blank. I'm thinking a right use of FILTER in the measureMax variable should work, but can't quite get it to work.

[PaulDBrown]

Please read this post:
How to get your question answered quickly 

(It would also help if the sample data included the blank values causing the problem for the MAX measure)

[Wendeley-North · Accepted solution]

Hi Paul,

Thanks for the reply, I have indeed read the post prior to posting, but in this instance thought -perhaps wrongly - that the solution would be close to what was already posted, even without sample data.

In any case, I have resolved it myself.

Here's the solution for in case anyone else find its useful:

maxCountry = 
VAR vals = 
    SUMMARIZE('Table', 'Table'[Country], "countryQuantity", SUM(Table[Quantity]))
VAR measureMax = 
    MAXX( FILTER(vals, Table[Country] <> ""), [countryQuantity])
VAR maxCountryName = 
    CALCULATE(MAXX(FILTER(vals, [countryQuantity] = measureMax), [Country]))
RETURN
    maxCountryName & " (" & FORMAT(measureMax, "0.00%") & ")"