Forum Discussion

setis's avatar
setis
Post Partisan
6 years ago
Solved

Simple FILTER calculation

Dear experts, 

 

I have 

Table1:

EntryDoc TypeDocNr
15551121574028
15551121574028
15551121574028
15551121574028
155511315191
155511315191

 

Table Sales:

Sales 
DocNrAmount
1574028121
1574028100
1574028115
1574028115
1574028188
157402888
1574028488
1574028496

 

Table Credit

DocNrAmount
15191454
1519115
15191158
15191188
15191184
15191487
1519148
1519129

 

I am looking for a measure to place in a matrix with "Entry" from Table 1 on Rows that gives me the amount that belong to Sales and the amount that belongs to Credit. 

 

What I'm doing for Sales is

 

 

Sales = 
CALCULATE(
  SUM('Sales'[Amount]);
FILTER('Sales';'Sales'[DocNr] = MAX('Table1'[DocNr])))

 

 

But I can see that the MAX is not right. I need to do a Doc type = 2 in there, but I can't figure out how. 

 

Could somebody help, please?

Need Help
  • littlemojopuppy's avatar
    littlemojopuppy
    6 years ago

    Your data model should have Table 1 with joins to Table Sales (Table1.DocNr -->Table Sales.Sales DocNr) and Table Credit (Table1.Doc Nr --> Table Credit.DocNr) independent of each other.

     

    From there, it should be fairly easy to create the measures:

    • Sales = SUM(Table Sales.Amount)
    • Credit = SUM(Table Credit.Amount)

     

    Hope this helps!

     

     

7 Replies

  • amitchandak's avatar
    amitchandak
    Super User
    6 years ago

    Try

    Sales =
    var _max = MAXX('Table1','Table1'[DocNr])
    return
    CALCULATE(
    SUM('Sales'[Amount]);
    FILTER('Sales';'Sales'[DocNr] =_max ))

  • itsmebvk's avatar
    itsmebvk
    Continued Contributor
    6 years ago

    setis  Can you try something like this

     

    Total =

    Var Table_Maximum_Doc = MAXX('TABLE','TABLE'[DocNr])

    Return

    CALCULATE(SUM('SALES'[Amount]);FILTER('SALES';'SALES'[DocNr] =Table_Maximum_Doc))

    • setis's avatar
      setis
      Post Partisan
      6 years ago

      Dear amitchandak and itsmebvk thanks a lot for trying to help. Unfortunately, the solution didn't work for me.

      I think that I didn't explain myself good enough, so I have just created a dummy file illustrating the issue. Please see it here: https://drive.google.com/file/d/1bQyBNs-7uu8e4sOiVRZnEkz57fKQXFMl/view?usp=sharing 

       

       

      The measures that I need is one for sales and one for Credit. The one for sales will find in Det_CLE the Doc Nr with the Doc Type "Sales" and use the Doc nr in the table sales to get the sum of the amount with that Doc No. 

       

      Same procedure with Credit.

       

      I hope that it is more clear now. If anyone could take a look at this and give me a hand, I would really appreciate it. 

       

      Thanks! 

      • littlemojopuppy's avatar
        littlemojopuppy
        Community Champion
        6 years ago

        Your data model should have Table 1 with joins to Table Sales (Table1.DocNr -->Table Sales.Sales DocNr) and Table Credit (Table1.Doc Nr --> Table Credit.DocNr) independent of each other.

         

        From there, it should be fairly easy to create the measures:

        • Sales = SUM(Table Sales.Amount)
        • Credit = SUM(Table Credit.Amount)

         

        Hope this helps!