Hi Folks,
Hope all are well.
Looking for help on the attached. There are two tabs:
- operations_viewer ongoing - a simple data table.
- Pivot - a simple pivot table based on values from the data table, two slicers added to help with filtering.
The extra step I am hoping to add but struggling to do is a filter or grouping that would allow the user to display only data that has a value of greater than 0.8 in any of the "M1", "M2" and "M3" columns across all dates selected. Does any one have any ideas?
Many thanks,
Alan
Hi Alan,
Probably not ideal, but you can add a Calculated Field to find the Max of each date's M1, M2 and M3, then filter on that field. See example attached.
Mynda
Hi Mynda,
Many thanks as always for the help - it definitely gets me much closer to the filtering I was hoping to add to the table. I was hoping to use "Row Max" as a slicer but it does not appear in the field list for slicers - is it possible to add slicers for calculated fields?
Sincerely,
Alan
No, Slicers aren't available for Calculated Fields 🙁 Maybe you'd be better off adding a helper column outside of the PivotTable that checked the Max for the row. If you format that column as a Table you can add a Slicer for the Table in Excel 2013 or later.
See example attached. You'll need to make sure the Table extended past the bottom of the PivotTable to allow for growth, likewise the range in the MAX formula.
You could always hide this column from users.
Mynda
This is great Mynda! Many thanks for taking the time to resolve this for me - very helpful.
Alan
