Sum all aggregated values from related measure

[#1]

Hi everyone,

I have loaded the following data set in my dashboard:

What I want to get is a list of all the managers, and the total sum of sales of the wholesalers they have had activity with.

For exemple, as you can see, manager A has worked with wholesalers 1 and 3. Wholesaler 1 has a total sales of 30 (10+20), and 3 has 110 (50+60). Therefore, the resulting outcome for A should be 140 (30+110).

Another example:manager B has only worked with wholesaler 1, so the desired outcome for B should be 30 (10+20).

Final example: C has worked with wholesaler 2, which has a total amount of sales of 70, so that's what we want for C.

So the formula should be something like Sum("all the sales of his wholesalers").

This is what I've tried: Sum(Aggr(Sum(value), wholesaler))

When I bring this to a table, the results are interesting:

It shows two rows, and they are both right! But for some reason, I only get values for two of the managers. No results for managers B, D and F. And I really don't understand why. Am I taking a wrong approach? Is there anything wrong with the Aggregate?

Thanks for your help.

[swuehl]

Have a look at

Pitfalls of the Aggr function

If I understood your case correctly, you can try with

Sum(Aggr(NODISTINCT Sum(value), wholesaler))

[sunny_talwar · Accepted solution]

Or this:

=Sum(Aggr(Sum(TOTAL <Wholesaler> value), Manager, Wholesaler))

[swuehl]

And if you want to select a Manager, but keep the aggregated value unchanged, clear the selection using set analysis

Sum(Aggr(NODISTINCT Sum({<Manager>} value), wholesaler))

[#5]

Thank you very much, guys. Both solutions worked, but when I escalated it to the real deal, the second one worked better.

[swuehl]

Just for interest, what do you mean with 'better'?