Calculate Value of Accounts which have been Called on by sales team

[Dlahey]

Hi,

I am using three data tables:

1) sales by account 2) account and 3) calls. 

I have created a formula for a distinct count on # of accounts based on a filter for being a target account:

# of Target Accounts = calculate(DISTINCTCOUNT(Account[Id]),Account[Target Flag]="Yes")

Then I have created a formula for the value of the target accounts:

Value of Target Accounts = calculate(SUM(Sales_Data[Total Sales]),Account[Target Flag]="Yes")

Another formula is how many distinct accounts have been called on:

# of Target Account Reach = calculate(distinctcount('Call'[Account]),Account[Target Flag]="Yes")

Now what I am having difficulty doing is calculating the Value of the Target accounts that have been called on:

can someone help me out here - I am trying to do a sum of [Total Sales] where Account Target Flag = Yes and distinctcount Call is greater than 1

my formula I've tried is:

Value of Target Account Reach = calculate(sum(Sales_Data[Total Sales]),Account[Target Flag]="Yes",distinctcount('Call'[Account])>1)

Thanks in advance,

Dave

[PaulOlding · Accepted solution]

Hi @Dlahey 

Here's a measure that builds a list of reached accounts first.

We can then use that to filter a calculation very similar to your Value of Target Accounts measure

Value of Target Account Reach = 
VAR _ReachedAccounts = VALUES('Call'[Account])
VAR _Result = 
CALCULATE(
    SUM(Sales_Data[Total Sales]),
    Account[Target Flag]="Yes",
    Account[Id] IN (_ReachedAccounts)
)
RETURN
    _Result

if you prefer, you could make the code DRY by referencing your Value of Target Accounts measure instead

Value of Target Account Reach = 
VAR _CalledAccounts = VALUES('Call'[Account])
VAR _Result = 
CALCULATE(
    [Value of Target Accounts],
    Account[Id] IN (_CalledAccounts)
)
RETURN
    _Result

both these options give the same result.

[Dlahey]

you just made my life sooo much easier - thank you so so much!!!!

[Tanushree_Kapse]

Hi @Dlahey ,

Try thi out:
Value of Target Account Reach = IF(AND(Account[Target Flag]="Yes", distinctcount('Call'[Account])>1), sum(Sales_Data[Total Sales]))

Mark this as a solution, if I answered your question. Kudos are always appreciated.

Thanks!

[Dlahey]

Hi,

Thanks so much for your help! Unfortunately, this is not working - see below error:

a few variable names are slightly different but aligned to the above example