Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
Solved

Filter problem

Dear all,

 

I have 2 tables:

 

Detailed Cust_ Ledg_ Entry

and

Sales Invoice Line
 

 

Both tables are linked to a look up customer table. But there is not relationship between each other.

 

I'm looking for a measure that per Customer Nr, gives me the SUM of the [Amount w VAT] from Sales Invoice Lines of all the Document No in Sales Invoice Lines (second table), that have a match in Detailed Cust_ Ledg_ Entry (first table).

 

The problem I have with the following measure is that the results are not correct when I place the measure with customers on rows: 

 

 

 

 

 

 

SalesFee = 
CALCULATE (
    SUM ( 'Sales Invoice Line'[Amount w VAT] );
    FILTER (
        'Sales Invoice Line';
        'Sales Invoice Line'[Document No_]
            = CALCULATE (
                MAX ( 'Detailed Cust_ Ledg_ Entry'[Document No_] );
                FILTER (
                    'Detailed Cust_ Ledg_ Entry';
                    'Detailed Cust_ Ledg_ Entry'[Document Type] = 2
                )
            )))

 

 

 

 

When I place this measure with Cust_Ledger Entry No_ on rows, the results line by line are correct. But with the customers on rows, it takes the results for only one line.

 

I think that the problem is in the filter. The MAX that I have to put in, when placed on customers, it gives me only the results for only one line. I probably have to build the filter with IN and/or Summarize but I'm not sure how.

 

I have reproduced the issue on a dummy report here: https://drive.google.com/file/d/1FWfP8nCVru8DYGrucvztsu2P5jXw3LCg/view?usp=sharing

 

I would really appreciate any kind of help. 

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    setis 

     

    I think you are linking the tables in a right way for calculation, try create a relationship for the two tables and alter a bit. 

    And your formula should work after remove "{ }": 

     

    Measure = 
CALCULATE(
    SUM('Sales Invoice Line'[Amount w VAT]),
    FILTER(
        'Sales Invoice Line',
        'Sales Invoice Line'[Document No_] in VALUES('Detailed Cust_Ledg_entry'[Document No_])),
        FILTER('Detailed Cust_Ledg_entry','Detailed Cust_Ledg_entry'[Document Type]=2))

     


    Best regards 

    Paul Zheng

     

6 Replies

    • setis's avatar
      setis
      Post Partisan
      6 years ago

      I'm sorry, part of my original post was deleted. (I am copying the below on the original post)

       

      Both tables are linked to a look up customer table and I need a measure that gives me the amount from Sales Invoice Lines when the Doc type is 2 and the Document numbers on both tables is the same. 

       

      The problem I have with the following measure is that the results are not correct when I place the measure with customers on rows: 

       

       

       

       

      SalesFee = 
CALCULATE (
    SUM ( 'Sales Invoice Line'[Amount w VAT] );
    FILTER (
        'Sales Invoice Line';
        'Sales Invoice Line'[Document No_]
            = CALCULATE (
                MAX ( 'Detailed Cust_ Ledg_ Entry'[Document No_] );
                FILTER (
                    'Detailed Cust_ Ledg_ Entry';
                    'Detailed Cust_ Ledg_ Entry'[Document Type] = 2
                )
            )))

       

       

       

      I think that the problem is the MAX that I have to put in the filter that when placed on customers, it gives me only the results for one line. I probably have to build the filter with IN and/or Summarize but I'm not sure how