Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
setis
Post Partisan
Post Partisan

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?

2 ACCEPTED SOLUTIONS

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.

Data Model.PNG

 

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

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

Results.PNG

 

Hope this helps!

 

 

View solution in original post

Results based on the PBIX you provided...

Data Model.PNGResults.PNG

View solution in original post

7 REPLIES 7
itsmebvk
Continued Contributor
Continued Contributor

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

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 

 

Capture5.PNG

 

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! 

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.

Data Model.PNG

 

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

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

Results.PNG

 

Hope this helps!

 

 

@littlemojopuppy Thanks for this. 

 

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

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

Results based on the PBIX you provided...

Data Model.PNGResults.PNG

amitchandak
Super User
Super User

Try

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

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

Check out the October 2024 Power BI update to learn about new features.

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.