Calculated column for account-customer level

[tomislav_mi]

Hello guys,

Hope you can help!

I have a table like below and would need to create a calcualted columns in orange.
Basically what I am trying to solve is to create column that creates channel labels based on SalesAccount column.
If SalesAccount is 0 then I would look at only active ChannelAccount from specific month and ChannelCustomer will
become more accurate ChannelActive. 

Month	Account	Customer	ChannelAccount	ChannelCustomer	SalesAccount	ChannelActive
Jan	GutG	ABBA	Direct	Both	50	Both
Jan	Baloon	ABBA	Reseller	Both	45	Both
Feb	GutG	ABBA	Direct	Both	50	Direct
Feb	Baloon	ABBA	Reseller	Both	0	Direct

Thanks to everybody who wants to take a shot at solving this challenge.

Best!

[v-jayw-msft]

Hi @tomislav_mi ,

Please refer this formula:

Column = 
var _min = CALCULATE(MIN('Table'[SalesAccount]),ALLEXCEPT('Table','Table'[Month]))
var _active = CALCULATE(MAX('Table'[ChannelAccount]),FILTER(ALLEXCEPT('Table','Table'[Month]),'Table'[SalesAccount]<>0))
return
IF(_min=0,_active,'Table'[ChannelCustomer])

If I misunderstood your meaning, please show more details.

Best Regards,

Jay

[tomislav_mi]

@v-jayw-msft @MFelix 

Thank you very much for your help.
The proposed solution from above is not the right way to go.

I will try to formulate the problem in other words:

The process goes like this:
1) identify the channel account that is active in an observed month ( column Active Channel Account refers to that)
2) use that active channel for a customer level (desired column Active Channel Customer )
3) if there is more than 1 value then Active Channel Customer is going to be "Both"

Index	Report Date	Account	Customer	Channel Account	Payment	Active	Active Channel Account	Active Channel Customer
25205	6/30/2022 0:00	a1	a	Direct		FALSE		Reseller
25215	6/30/2022 0:00	a2	a	Reseller	500	TRUE	Reseller	Reseller
25298	6/30/2022 0:00	a3	a	Direct		FALSE		Reseller
35373	6/30/2022 0:00	a4	a	Reseller	600	TRUE	Reseller	Reseller
25205	7/31/2022 0:00	a1	a	Direct		FALSE		Direct
25215	7/31/2022 0:00	a2	a	Reseller		FALSE		Direct
25298	7/31/2022 0:00	a3	a	Direct	45454	TRUE	Direct	Direct
35373	7/31/2022 0:00	a4	a	Reseller		FALSE		Direct
15135	6/30/2022 0:00	b1	b	Direct	1234	TRUE	Direct	Both
15354	6/30/2022 0:00	b2	b	Reseller	8745	TRUE	Reseller	Both

[MFelix]

Hi @tomislav_mi ,

Is this case how do you know that the specific customr is Direct? In the last line you have Direct e Reseller but you select the Direct whow do you know what is the value to pick up, also do you want to have that calculation only on that specific month, or for all months forward also?