Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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%") & ")"
User | Count |
---|---|
125 | |
108 | |
99 | |
63 | |
62 |
User | Count |
---|---|
135 | |
116 | |
101 | |
71 | |
61 |