cancel
Showing results for
Did you mean:
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
Frequent Visitor

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

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

)

)

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
Frequent Visitor

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

sumx(customer;

sumx(Filter(AppointmentTable; customer[id15] = appointment[id15]);

sumx( Filter(SalesTable; sales[id7] = customer[id7] && sales[date] = appointment[date]);

sales[revenue]

)

)

Announcements

#### 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

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

#### Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 372 members 3,751 guests
Recent signins: