Key Accounts logics based on the conditions goes wrong

[dinesharivalaga]

Hi Experts,

I am working on the scenario to make the accounts into Key Accounts or NOT.

But in some conditions , it is not working in the table.

Below the requirements and sample inputs.

Key Account Logic :
---------------------

if "Delivery Model"="Embedded" or Revenue > 500K ="Yes" or "PACE Account"="Yes" or "Engagement run with DPOT"="Yes" or "Account requires focus"="Yes" then it is "Key Account" , In condition "Revenue > 500K" , you have to sum all rows for the account names , if the sum is >500K then it is "Key Account" --> VAR keyaccountrule = CALCULATE([Total FY Forecast],ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))
If anyone of the above conditions are matching then the account should be "KEY ACCOUNT"

Below the Sharepoint list inputs & DAX created :

Here above marked 2 accounts are same name and PACE account is Yes but sum of Total forecast  is not more than 500k but it is "Key Account" but this account is not showing in the Key Accounts bookmarks.

Key account Rule =
IF('Test Delivery Updates'[Delivery Model] = "Embedded","Yes",IF('Test Delivery Updates'[PACE Account]="Yes","Yes",IF('Test Delivery Updates'[Engagement run with DPOT]="Yes","Yes",IF('Test Delivery Updates'[Account requires focus]="Yes","Yes",IF('Test Delivery Updates'[Delivery Model]<> "Embedded" && 'Test Delivery Updates'[PACE Account]=BLANK() && 'Test Delivery Updates'[Engagement run with DPOT]=BLANK() && 'Test Delivery Updates'[Account requires focus]=BLANK(),"Yes","No")))))

Key Account Rule 1 =

VAR keyaccountrule = CALCULATE([Total FY Forecast],ALLEXCEPT('Test Delivery Updates','Test Delivery Updates'[Account Name]))

RETURN

IF(keyaccountrule > 500000,"Yes","No")

Please help to achieve this requirement ..

Thanks

DK

[v-zhengdxu-msft · Accepted solution]

Hi @dinesharivalaga 

I've did some change and combined these two formulas of yours, Please try this:

Key account Rule =
VAR keyaccountrule =
    CALCULATE (
        [Total FY Forecast],
        ALLEXCEPT ( 'Test Delivery Updates', 'Test Delivery Updates'[Account Name] )
    )
RETURN
    IF (
        'Test Delivery Updates'[Delivery Model] = "Embedded"
            || 'Test Delivery Updates'[PACE Account] = "Yes"
            || 'Test Delivery Updates'[Engagement run with DPOT] = "Yes"
            || 'Test Delivery Updates'[Account requires focus] = "Yes"
            || (
                'Test Delivery Updates'[Delivery Model] <> "Embedded"
                    && 'Test Delivery Updates'[PACE Account] = BLANK ()
                    && 'Test Delivery Updates'[Engagement run with DPOT] = BLANK ()
                    && 'Test Delivery Updates'[Account requires focus] = BLANK ()
            )
            || keyaccountrule > 500000,
        "Yes",
        "No"
    )

Then in the filter pane, filter the Key account Rule equal to Yes.

Hope it can help.

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.