Yesterday, a customer asked me how to get the total from a max aggregation. This is an interesting question, and I wanted to share the answer with you.
Let's say we have three dimensions (Customers, Projects, Items) and we want to get the max number of items in each project:
Let's create a max aggregation on #Items (more info) and call it Max Items. Here you can see an example on a Pivot:
Now, let's create a new custom measure to sum all the max items in Projects: sum ( [Projects] , [Max Items] ) and call it Sum if Max Items,
Add this measure to a Numeric Indicator:
Tip: Adding a selector with the Customers dimension will yield the Sum of Max for each selected company.