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.
So I work for a retail company that provides appointments and I'm interested in getting a revenue per appointment metric. However, not everyone that buys something came to an appointment.
I have a transaction history table with Customer ID along with an appointment history table with the same customer ID. Both tables also have a date column.
What's the best way for me to sum sales of Customer IDs if they have a row in the appointment table on the same date?
Thanks!
Solved! Go to Solution.
Add another sumx like this:
sumx(customer;
sumx(Filter(AppointmentTable; customer[id15] = appointment[id15]);
sumx( Filter(SalesTable; sales[id7] = customer[id7] && sales[date] = appointment[date]);
sales[revenue]
)
)
I would use nested sumx filtering the rows, something like:
sumx(AppointmentTable;
sumx( Filter(SalesTable; sales[id] = appointment[id] && sales[date] = appointment[date]);
sales[revenue]
)
)
So that looks like a good solution but I have a hiccup. My sales table, appointment table, and customer table are all tied to Locations.
The sales table contains a 7 digit customer ID while the appointment table has a 15 digit customer ID. The accounts table has both of those.
I tried to make a related() in one of the sales or appointment tables but it would introduce ambiguity to Accounts and Locations. Is there a way for me to loop the sumx through accounts?
Would sales[id]=customer[sales id], customer[appointment id]=appointment[id], appointment[date]=sales[date] work?
Add another sumx like this:
sumx(customer;
sumx(Filter(AppointmentTable; customer[id15] = appointment[id15]);
sumx( Filter(SalesTable; sales[id7] = customer[id7] && sales[date] = appointment[date]);
sales[revenue]
)
)
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 |
---|---|
114 | |
100 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |