Announcements
cancel
Showing results for
Did you mean:
Creator

## Max value of a dimension aggregated by another dimension

Hello,

As of late, I've been trying to create a table which gives me the max value of a material regardless of the customer. If I don't make filters it works flawlessly, but when filtering it the scope changes and I it adapts to to that.

Below is the table without filters:

If I filter by material it works as I want. Image below:

But when I filter by Customer(image below), I don't get want I want, since wanted to see for A-500, B-1000, C-200.

I understand why this happens, but it's not what I want.

Is there any way to achieve what I am portraying here?

André Braga

1 Solution

Accepted Solutions
MVP

May be this

Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)) * Avg(1)

11 Replies
MVP

Try this:

Max(TOTAL <Material> {<Customer>} Value) * Avg(1)

Creator
Author

But I need to use the SUM() function on my Value metric. How can I do it with that? Thank you for helping!

MVP

May be this

Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)) * Avg(1)

Creator
Author

That's it! Thanks very much!! Could I ask you the purpose of the {<Customer>} and the Avg(1) ? I understand everything else apart from these two.

MVP

{<Customer>} is used to ignore selection in Customer field and Avg(1) is used to make sure that you only see those rows where you have selected a particular customer because if you won't use Avg(1), you will see all customers (because we ignored selection in it).

Creator
Author

Ah got ya, that makes sense. I was doing something like the Avg(1) but with an IF before the expression itself.

Thanks for help Sunny!

MVP

That should work too..

If(Customer, Max(TOTAL <Material> {<Customer>} Aggr(Sum({<Customer>} Value), Material, Customer)))

Partner - Contributor III

Hi Sunny

I'm new to these advanced calculations using set analysis, so could you please tell me why was <Material> used in the equation

MVP

That is field name used together with TOTAL qualifier. It is saying that show the Total at the Material level for each row

Community Browser