topic Create a column by writing DAX to sum FY forecast according to account names with some logics in DAX Commands and Tips

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

Anonymous — Tue, 03 Sep 2024 06:29:26 GMT

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

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

Rupak_bi — Tue, 03 Sep 2024 07:06:13 GMT

Hi Anonymous ,

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

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

Anonymous — Tue, 03 Sep 2024 07:32:33 GMT

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 ..

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

Rupak_bi — Tue, 03 Sep 2024 12:10:47 GMT

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")

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

Anonymous — Tue, 03 Sep 2024 12:37:43 GMT

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.

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

Rupak_bi — Tue, 03 Sep 2024 12:52:35 GMT

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

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

Anonymous — Tue, 03 Sep 2024 13:10:46 GMT

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.

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

Rupak_bi — Tue, 03 Sep 2024 14:06:39 GMT

Hi Anonymous ,

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").

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

Anonymous — Wed, 04 Sep 2024 02:36:15 GMT

Hi Anonymous ,

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.

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

Anonymous — Wed, 04 Sep 2024 05:14:13 GMT

Anonymous 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