Forum Discussion
Calculated column for account-customer level
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!
3 Replies
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?
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
Anonymous 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
