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?
Go to Solution.
Add another sumx like this:
sumx(Filter(AppointmentTable; customer[id15] = appointment[id15]);
sumx( Filter(SalesTable; sales[id7] = customer[id7] && sales[date] = appointment[date]);
View solution in original post
I would use nested sumx filtering the rows, something like:
sumx( Filter(SalesTable; sales[id] = appointment[id] && sales[date] = appointment[date]);
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?
Learn how to create your own user groups today!
Click here to read more about the October 2021 Release!
Check out the new Power Platform Community Connections gallery!
Mark your calendars and join us for our next Power BI Dev Camp!