Power Pivot Principles: MAXX with Filter Criteria

Welcome back to our Power Pivot blog. Today, we discuss how to include filters in the MAXX function.

Last week we covered how the MAXX functions works, you can read more about it here.

This week we’d like to expand on the MAXX function a little, and how you how we can use other functions that return with a table to alter the MAXX function’s results.

For this example, let’s look at the following Table (the capitalisation is deliberate):

As a quick refresher, the following measure calculates the greatest amount spent on apples:

This measure returns with the value of $690.00, which is the greatest amount spent on apples without any filters.

What we did not cover last week is that the MAXX function can work with other functions too.  We can use the FILTER function to filter out customers that do not fulfil the criterion ‘Customer Type’ = 1.

The FILTER function returns with a <table> (quoting the MAXX syntax from last week), therefore we are able to use these two functions together. Exporting this to a PivotTable yields the following result:

The greatest amount spent on apples by customers who fall in the ‘1’ category is $480.00.

That’s it for this week, until next time. Happy Pivoting!

Stay tuned for our next post on Power Pivot in the Blog section. In the meantime, please remember we have training in Power Pivot which you can find out more about here. If you wish to catch up on past articles in the meantime, you can find all of our Past Power Pivot blogs here.