cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rynelees
Helper I
Helper I

Making a measure based off of two tables having the same Customer ID on the same date

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!

1 ACCEPTED 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]

   )

)

View solution in original post

3 REPLIES 3
Rodrigo_Carvalh
Advocate II
Advocate II

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]

   )

)

View solution in original post

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

Power BI October Update 2021.jpg

Power BI Release

Click here to read more about the October 2021 Release!

Community Connections 768x460.jpg

Community & How To Videos

Check out the new Power Platform Community Connections gallery!

Teds Dev Camp Oct. 2021 768x460.jpg

Power BI Dev Camp - October 28th, 2021

Mark your calendars and join us for our next Power BI Dev Camp!