Create measure with data filtered in different table

[hexitated]

Hello everyone,

I don't really get whats the easiest way to solve my problem.

I have a matrix which shows all sales of our new customers from 2020.

This matrix contains information from two different tables:

Table 1 (Transactions)

Table 2 (Customers)

I have a Sales measure which calculates all sales from the transactions table based of the customer nr which is in the same table. The customer nr. is connected to the customers table and then shows the exact name of the customer. I added a calculated column to the customers table that shows if the customer is a new customer (YES) or not (NO).

I manually filter out all old customers in my visual.

Now I want to create a card or kpi that shows the sales amount of new customers in relation to the total sales.

Total sales obviously is not a problem - I just use my measure which gives me the exact amount based on my date filter.

But I'm having a hard time getting the number for my new customers. So basically the number that is the grand total in my matrix.

I guess I can't access this total directly but have to calculate a measure with the exact same filters?

So I thought that would mean that I need to filter my customers table like that:

= FILTER(Debitor,Debitor[NewCustomer]="Yes")

Then I would have all new customers. But how do I use all of the customer ids from this returned table to generate sales through my other measure.

In other words I want to use the formula of my sales measure but to only consider these customers with these id's that came back from my filter I wrote above.

Do you guys know what I mean? I think it shouldn't be that difficult but I don't know if I am on the right track.

Thank you!!

[amitchandak · Accepted solution]

@hexitated , Debitor is same as the customer?

you can try a measure like

calculate(distinctCOUNT(Sales[Customer Id]),FILTER(Debitor,Debitor[NewCustomer]="Yes"))

[Jihwan_Kim · Accepted solution]

Hi, @hexitated 

Please correct me if I wrongly understood.

Please try to write your DAX measure like below.

YourMeasure =

Calculate ( measure for your old and new customers, FILTER(Debitor,Debitor[NewCustomer]="Yes"))

If it is OK with you, please share your pbix file, then I can try to look into it to come up with a desirable solution.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[Jihwan_Kim · Accepted solution]

Hi, @hexitated 

Please correct me if I wrongly understood.

Please try to write your DAX measure like below.

YourMeasure =

Calculate ( measure for your old and new customers, FILTER(Debitor,Debitor[NewCustomer]="Yes"))

If it is OK with you, please share your pbix file, then I can try to look into it to come up with a desirable solution.

Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!

[hexitated]

Thanks, that worked perfectly 🙂

[hexitated]

If have another question. In a next step I'd like to get the average amount of sales for every customer. So in my case the new measure brought up a value like 15k which is the sum of the revenue of 3 Customers:

= CALCULATE([Revenue], FILTER(Debitor,Debitor[NewCustomer]="YES"))

But no I want to divide this number by the actual amount of customers. The problem is that I can't use the same filter because it will get a number above 10 since not every customer had sales. In fact only about 3 out of all the customers. Can I somehow use the newly created measure and extract the summands or do I have to create something new?

I find it a bit difficult to create something new because I would need to refer to the transactionstable to find out if customer x had sales there.

Thanks a lot!!

[Jihwan_Kim · Accepted solution]

Hi, @hexitated 

Thank you for your question.

I think you can use AVERAGEX function with your filter.

For instance, 

outcome = calculate ( AVERAGEX ( customerstable, revenue), 

                                                                                             your filter )

Terribly sorry that without actually using sample file to create measures in pbix file, I cannot write the exact name of other measures and table/column names.

I hope the above DAX measure is OK to understand.

Or, please kindly share a sample data file, then I can try to come up with a desirable outcome.

Thank you very much.

[hexitated]

Thanks! That worked 🙂

[amitchandak · Accepted solution]

@hexitated , Debitor is same as the customer?

you can try a measure like

calculate(distinctCOUNT(Sales[Customer Id]),FILTER(Debitor,Debitor[NewCustomer]="Yes"))