03-22-2020 10:41 AM - last edited 04-27-2020 11:01 AM
This one or some variation of it comes up constantly in the forums so I'm surprised that there is no pattern in the Quick Measure Gallery for dealing with it. The problem generally goes along the lines of this:
"I have this table of data with these items in it and values for various blah. I need to create a measure that returns which item has the lowest/highest sum/average/blah."
That's not a direct quote, I'm paraphrasing...
Anyway, the pattern looks like this:
Lookup Min = VAR __Table = SUMMARIZE( 'Table', [Item], "__Value",SUM('Table'[Value]) ) VAR __Min = MINX(__Table,[__Value]) RETURN MINX(FILTER(__Table,[__Value] = __Min),[Item]) Lookup Max = VAR __Table = SUMMARIZE( 'Table', [Item], "__Value",SUM('Table'[Value]) ) VAR __Max = MAXX(__Table,[__Value]) RETURN MAXX(FILTER(__Table,[__Value] = __Max),[Item])
@Greg_Deckler Iterator X-functions are great but you can also do this without them.
Lookup Max = VAR _TopRow = TOPN ( 1, VALUES ( 'Table'[Item] ), CALCULATE ( SUM ( 'Table'[Value] ) ) ) VAR _TopItem = SELECTCOLUMNS ( _TopRow, "Item", 'Table'[Item] ) RETURN _TopItem
Many thanks for your solution. But is there a way to calculate the same with help of calculated column (not as measure as in your example)?
Thanks in advance!
@Alyona_BI - Absolutely, the same technique will work with a calculated column. You may have to use EARLIER to filter down to the correct rows. Hard to be specific.
my table looks like this, so there are multiple repeating scenario ids, and for example COP-0001 will have step numbers 1-35, i basically need to return the lowest, which should be 1 and the highest, 35, and access those date records.
Im trying your code, but not sure about the __Table and ___Value parts/
Could you paste an example code following your pattern
Business Unit Stream Scenario ID Step Number Planned Date Execution Date