Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
hexitated
Helper I
Helper I

Create measure with data filtered in different table

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

3 ACCEPTED SOLUTIONS
amitchandak
Super User
Super User

@hexitated , Debitor is same as the customer?

 

you can try a measure like

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

View solution in original post

Jihwan_Kim
Super User
Super User

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



Microsoft MVP



If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question



View solution in original post

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.



Microsoft MVP



If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question



View solution in original post

6 REPLIES 6
Jihwan_Kim
Super User
Super User

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



Microsoft MVP



If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question



Thanks, that worked perfectly 🙂

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



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.



Microsoft MVP



If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.




LinkedInVisit my LinkedIn page




Outlook BookingSchedule a short Teams meeting to discuss your question



Thanks! That worked 🙂

amitchandak
Super User
Super User

@hexitated , Debitor is same as the customer?

 

you can try a measure like

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

Full Power BI Video 20 Hours YouTube
Microsoft Fabric Series 60+ Videos YouTube
Microsoft Fabric Hindi End to End YouTube

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

Check out the June 2025 Power BI update to learn about new features.

June 2025 community update carousel

Fabric Community Update - June 2025

Find out what's new and trending in the Fabric community.