cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rynelees Regular Visitor
Regular Visitor

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

Accepted Solutions
Highlighted
Rodrigo_Carvalh Frequent Visitor
Frequent Visitor

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

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]

   )

)

3 REPLIES 3
Rodrigo_Carvalh Frequent Visitor
Frequent Visitor

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

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]

   )

)

rynelees Regular Visitor
Regular Visitor

Re: Making a measure based off of two tables having the same Customer ID on the same 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?

Highlighted
Rodrigo_Carvalh Frequent Visitor
Frequent Visitor

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

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]

   )

)

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 372 members 3,751 guests
Please welcome our newest community members: