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