Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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!
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
@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 |
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?
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsCovering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |