Forum Discussion

hexitated's avatar
hexitated
Helper I
5 years ago
Solved

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 differ...
Need Help
  • amitchandak's avatar
    amitchandak
    5 years ago

    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's avatar
    Jihwan_Kim
    5 years ago

    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's avatar
    Jihwan_Kim
    5 years ago

    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's avatar
hexitated
Helper I
5 years ago

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's avatar
Jihwan_Kim
Super User
5 years ago

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's avatar
    hexitated
    Helper I
    5 years ago

    Thanks! That worked 🙂