I am looking for a (hopefully) simple DAX measure to solve the following problem.
I have a range consisting of a number of employees, a chosen product, and their savings in each product. In a different table, I need to create a measure that returns the name of the product that contains the largest combined sum of savings. For e.g. Person 1, this would be "Product B", because the 750,000 in Product B exceeds the 500,000 in Product A (had it been opposite, the measure should return "Product A").
I am working in PowerPivot in Excel 2013 and have previously relied on "FIRSTNONBLANK" since the ranges have contained no dublicate values. However, that solution is no longer sufficient. Any help is greatly appreciated.
However, because I use the names ("Person 1", "Person 2", etc.) as the row filter in the Pivot Table, only one product is currently returned as value. So, the rank function simply returns "1" for all employees, and hence it is not possible to filter.