Simple FILTER calculation

[setis]

Dear experts, 

I have 

Table1:

Entry	Doc Type	DocNr
155511	2	1574028
155511	2	1574028
155511	2	1574028
155511	2	1574028
155511	3	15191
155511	3	15191

Table Sales:

Sales
DocNr	Amount
1574028	121
1574028	100
1574028	115
1574028	115
1574028	188
1574028	88
1574028	488
1574028	496

Table Credit

DocNr	Amount
15191	454
15191	15
15191	158
15191	188
15191	184
15191	487
15191	48
15191	29

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?

[littlemojopuppy · Accepted solution]

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!

[itsmebvk]

@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]

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 · Accepted solution]

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!

[setis]

@littlemojopuppy Thanks for this. 

These would be many-to-many relationships. Wouldn't there be any issues with that for this purpose?

[littlemojopuppy]

Do the results I posted a screen snip of seem reasonable based on the sample data you provided?

[littlemojopuppy · Accepted solution]

Results based on the PBIX you provided...

[amitchandak]

Try

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