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
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

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!