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, I'm new to DAX and I'm unfortunately not getting anywhere at this point. I have a table with sales data. Each sale is assigned to a sales channel. I want to use a Sankey chart to show how customers churn between sales channels when they order multiple times.
My thought was: I use the sales channel column given so far and add another column which contains the sales channel of the next order, if there is another order. So it should show the next sales channel if the customer number stays the same and the column "Order / Customer" grows by 1. Unfortunately I have no idea how to formulate this in DAX.
In Sankey I would use the actual sales channel as the start and the new column as the target. Does this make sense, and what would happen if customers order only once and then the new column remains empty?
Here is my current table with orders:
order_id | customer_id | sales channel | order / customer | following sales channel |
1 | 11 | Amazon | 1 | ebay |
2 | 11 | ebay | 2 | - |
3 | 12 | Amazon | 1 | - |
4 | 13 | Shop | 1 | Amazon |
6 | 13 | Amazon | 2 | Amazon |
7 | 13 | Amazon | 3 | - |
If my approach does not make sense, please feel free to give me a better suggestion. It doesn't matter how, but I would like to be able to show the churn between distribution channels.
Thank you very much!
Solved! Go to Solution.
@mno3nrg , In case you need following sales channel, a new column
following sales channel =
var _max = minx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] > earlier([order_id])),[order_id])
return
maxx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] =_max ),[sales channel])
@mno3nrg , In case you need following sales channel, a new column
following sales channel =
var _max = minx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] > earlier([order_id])),[order_id])
return
maxx(filter(Table, [customer_id] =earlier([customer_id]) && [order_id] =_max ),[sales channel])
Covering 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 |
---|---|
46 | |
26 | |
22 | |
12 | |
8 |
User | Count |
---|---|
75 | |
51 | |
47 | |
16 | |
13 |