Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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.
Solved! Go to 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%") & ")"
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)
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%") & ")"
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
109 | |
98 | |
80 | |
64 | |
57 |
User | Count |
---|---|
145 | |
110 | |
91 | |
84 | |
66 |