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]);
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?
Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.
We spoke with Power BI Super User, Greg Deckler, about his charity work
Continue your learning in our online communities.