Join us for an expert-led overview of the tools and concepts you'll need to pass exam PL-300. The first session starts on June 11th. See you there!
Get registeredPower BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.
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!!
Solved! Go to Solution.
@hexitated , Debitor is same as the customer?
you can try a measure like
calculate(distinctCOUNT(Sales[Customer Id]),FILTER(Debitor,Debitor[NewCustomer]="Yes"))
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!!
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.
Schedule a short Teams meeting to discuss your question
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.
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.
Schedule a short Teams meeting to discuss your question
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!!
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.
Schedule 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:
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.
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.
Schedule a short Teams meeting to discuss your question
Thanks! That worked 🙂
@hexitated , Debitor is same as the customer?
you can try a measure like
calculate(distinctCOUNT(Sales[Customer Id]),FILTER(Debitor,Debitor[NewCustomer]="Yes"))
User | Count |
---|---|
84 | |
76 | |
74 | |
48 | |
39 |
User | Count |
---|---|
114 | |
56 | |
51 | |
42 | |
42 |