Create a column by writing DAX to sum FY forecast according to account names with some logics

[dinesharivalaga]

Hi Experts,

I am working on the scenario below :

If the sum of FY Forecast is >500K then it is key account otherwise not a key accounts.

ex; (Account name X - RPA offer type record has 200K, X GenAI has 100K and no other X records, then it is not key account.  E RPA record has 200K, E GenAI has 300K then it is key account)

We have to create a calculated column as Key Account to mark it as YES or NO based on the above table logic . 

Account Name	FY Forecast	Offer Type	Key Account (Yes or No)
X	200000	RPA
B	3455	GENAI
S	387355	PMO
E	200000	RPA
X	100000	GENAI
E	300000	GENAI

Please help to achieve this .

Thanks

DK

[Rupak_bi]

Hi @dinesharivalaga ,

Here is your solution

Key Account =

Var total_Forcast = Calculate(sum('Table (2)'[FY Forecast]),ALLEXCEPT('Table (2)','Table (2)'[Account Name]))

return

If(total_Forcast>=500000,"Yes","No")

If this works, Please accept as solution

[dinesharivalaga]

@Rupak_bi  Thanks a lot , logic is correct and works.

But i have done unpivot column for few selected columns , so that each accounts are now showing as a multiple rows as below , in this case this DAX is summing up all the FY forecast value for each account and giving YES for all rows.

Account Name	FY Forecast	Key Account Rule 2
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes
Agricopel - Brazil	€ 18,609	Yes

 in the above account , FY FOrecast is just 18609 , which is not KEY ACCOUNT , but due to the unpivot column method , single row was converted to multiple rows and summing up all the 27 rows (18609*27 = 502443) , so based on this logic it meets the logic and given YES for key accounts.

How should we add DISTINCT here ?

Please help ..

[Rupak_bi]

This can be done in a Measure as below

New Key Account 1 =

Var tab = SUMMARIZE('Table (2)','Table (2)'[Account Name],'Table (2)'[Offer Type],"Value", average('Table (2)'[FY Forecast]),"Acc",max('Table (2)'[Account Name]))

Var op = SUMX(tab,[Value])

return

If(op>=500000,"Yes","No")

[dinesharivalaga]

@Rupak_bi  Still the column is giving the same YES result for all the rows , even if it is not KEY Account..

So it is not distinct account names and still summing up all the values of each rows of a single account and giving YES again.

[Rupak_bi]

Plz share sample data with some duplicates to try it out at my end

[dinesharivalaga]

@Rupak_bi 

Account Name	FY Forecast	Key Account Rule 2
Agricopel - Brazil	€ 18,609	Should be NO as < 500000
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
Agricopel - Brazil	€ 18,609	Should be NO
National Grid Plc	€ 5,23,776	Should be YES , as > 500000
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
National Grid Plc	€ 5,23,776	Should be YES
UNILEVER PLC	€ 4,80,563	Should be NO as < 500000
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO
UNILEVER PLC	€ 4,80,563	Should be NO

All the accounts are giving YES in Key Account column due to the summing up of all the rows by each accounts. but it should not sum if account name is same else offer type is different then we can sum FY forecast for different offertype rows alone.

[v-rzhou-msft · Accepted solution]

Hi @dinesharivalaga ,

I suggest you to try code as below to create a calculated column.

Key Account =
VAR _Virtual =
    SUMMARIZE (
        'Delivery Updates',
        'Delivery Updates'[Account Name],
        'Delivery Updates'[Offer Type],
        'Delivery Updates'[FY Forecast]
    )
VAR _AcName = 'Delivery Updates'[Account Name]
VAR _FYForecast =
    SUMX ( FILTER ( _Virtual, [Account Name] = _AcName ), [FY Forecast] )
RETURN
    IF ( _FYForecast >= 500000, "Yes", "No" )

Result is as below.

Best Regards,
Rico Zhou

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

[dinesharivalaga]

@v-rzhou-msft  Thanks a lot , it worked well and i can see the right Key Accounts for the values.

Also like this same i was struggling to get the one more logic conditions as below :

Scope column logic :

if "Are there any changes between SOW Agreed Scope & Revised Scope?" is Yes and "Is the devaition between SOW Agreed scope and revised scope has major deviation?" is Yes --> Red

              if "Is the CR raised?" is yes or No then keep it Red

              if "Is the CR raised?" is yes and "Is the CR approved?" is No then keep it Red

              if "Is the CR raised?" is yes and "Is the CR approved?" is yes then change it to Green

if "Are there any changes between SOW Agreed Scope & Revised Scope?" is Yes and "Is the devaition between SOW Agreed scope and revised scope has major deviation?" is No --> Amber

              if "Is the CR raised?" is yes or No then keep it Amber

              if "Is the CR raised?" is yes and "Is the CR approved?" is No then keep it Amber

              if "Is the CR raised?" is yes and "Is the CR approved?" is yes then change it to Green

All the above questions are already available in the Sharepoint list source , based on the above logics I have to create a column for the accounts and then i can convert those colors into icons by conditional formatting like below .

Please help to make this solution 

Thanks

DK

[Rupak_bi]

Hi @dinesharivalaga ,

Your initial data set was having three columns and now its two. if this one is correct, then you can simply use an If statement to make the "Yes","No" Flag as below:
column = if(fy forcast >500K, "Yes","No").