cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Wendeley-North
Resolver I
Resolver I

Return category label and value after summarizing table.

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:

DateGrouping 1Item IDCountrySectorQuantity
29-Jan-99A12431232ABZZ-100
26-Feb-99A12312333AEZC10
29-Jan-99B16453453ACZA100
26-Feb-99B13232322AEZF50

 

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.

1 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%") & ")"

 

View solution in original post

2 REPLIES 2
PaulDBrown
Super User II
Super User II

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)





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






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%") & ")"

 

View solution in original post

Helpful resources

Announcements
PBI User Groups

Welcome to the User Group Public Preview

Check out new user group experience and if you are a leader please create your group!

MBAS on Demand

Microsoft Business Applications Summit sessions

On-demand access to all the great content presented by the product teams and community members! #MSBizAppsSummit #CommunityRocks

Get Ready for Power BI Dev Camp

Power BI Dev Camp - June 24th

Mark your calendars and join us for our next Power BI Dev Camp!

Top Solution Authors
Top Kudoed Authors