Summarize next level

Question

I'm having a simple data model:

• Sales: Store, Sales
• Stores: Store, Account, Manager

Sampe file here

I want to compare the sale of each account with the sales of the account's manager.

While I know I can simply build a pivot with the Manager and the Account as rows and use CALCULATE(SUM(Sales[Sales]),ALL(Stores[Account]), I'm looking for a solution that works even if the Manager is not part of the pivot table.

I figured I can find the manager's name on the fly with FIRSTNONBLANK - and then use Sales_Manager:=CALCULATE(SUM(Sales[Sales]),ALL(Stores[Account]),FILTER(Stores,Stores[Manager]=FIRSTNONBLANK(Stores[Manager],TRUE))) - but that does not work.

Here is the current (wrong) result:

What would be the best way to calculate Sales_Manager?

Answer 1

Here ya go... :)

=CALCULATE([Sales_Total], ALL(Stores), VALUES(Stores[Manager]))