I would like to create a table where one cell shows the max value.
For example, I have a set of data like the following which shows how much spending a person had in a given category:
| Person | Category | YTM Dollars | Month | Year | % Change Net Spending |
| Mike | Shoes | $100 | May | 2019 | 50% |
| Mike | Books | $50 | May | 2019 | 50% |
| Mary | Shoes | $100 | May | 2019 | 50% |
| Mary | Books | $200 | May | 2019 | 50% |
I want a table that shows which people had the largest % increase in spending year to month and also which category had the most year to month (YTM) net spending for that person.
| % Change Net | 50% | 10% |
| Person | Mike | Mary |
| Category | Shoes | Books |
Except, when I write the set expression for the category cell as something like
Mode({$<Date_num={"$(=Max({$} Date_Num))"},[YTM Dollars Spent]={"$(=Max({$<Date_Num={'$(=Max({$} Date_Num))'}>} [YTM Dollars Spent]))"}>} [Category])
I get something like:
| % Change Net | 50% | 10% |
| Person | Mike | Mary |
| Category | - | Books |
I think it's looking for the category that matches the max YTM overall (i.e. Max(YTM) = $200) and not within the set of the other cells. How do I write the set correctly to find the right category (i.e. Max(YTM)=$100 for column 1 [Mike, May 2019] and Max(YTM)=$200 for column 2 [Mary, May 2019])?
.png "Former Employee"){kind=icon}
