Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

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
Community Champion
Community Champion

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

 

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.