Counting Revenue For Companies and only count budget for some

[PeterStuhr]

Hi all,

I have kind of a complex problem.

We have the following tables:

Account Manager Table:

Account Manager	Office
Anders Andersen	Denmark
Peter Petersen	Englang
Mads Madsen	Sweden

Order Intake Table:

Company	Office	Account Manager	Revenue
A	Denmark	Anders Andersen	10000
B	Denmark	Anders Andersen	10000
C	Denmark	Anders Andersen	10000
D	Englang	Peter Petersen	10000
E	Englang	Peter Petersen	10000
F	Sweden	Anders Andersen	10000
G	Sweden	Mads Madsen	10000
H	Sweden	Mads Madsen	10000
I	Sweden	Mads Madsen	10000

Budget Table:

Company	Office	Budget
A	Denmark	20000
B	Denmark	20000
C	Denmark	20000
D	Englang	20000
E	Englang	20000
F	Sweden	20000
G	Sweden	20000
H	Sweden	20000
I	Sweden	20000

I have set up a visual showing, dependent on the office selected:

Order Intake and budget.

If selecting Sweden, I would like to see:

Order Intake for aompany FGHI, but only compare to sum of Budget for Mads' accounts. Right now, I am also seeing budget for Anders, since he has a single account in Sweden. In our company we have 1000s of accounts - and sometimes an account manager has a single account for another Office than his own. Which is alright - that is revenue owned by the office. But we dont want his budgets to count as well. if that makes sense.

[v-cherch-msft · Accepted solution]

Hi @PeterStuhr 

You may link the three tables first.Then create two measures as below to get the sum.

SUM_Revenue =
CALCULATE (
    SUM ( 'Order Intake'[Revenue] ),
    FILTER (
        'Order Intake',
        'Order Intake'[Account Manager]
            = SELECTEDVALUE ( 'Account Manager'[Account Manager] )
    )
)

SUM_Budget = CALCULATE(SUM(Budget[Budget]),FILTER('Order Intake','Order Intake'[Account Manager]=SELECTEDVALUE('Account Manager'[Account Manager])))

Regards,