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
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?
Solved! Go to 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:
Hope this helps!
@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
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.
From there, it should be fairly easy to create the measures:
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...
Try
Sales =
var _max = MAXX('Table1','Table1'[DocNr])
return
CALCULATE(
SUM('Sales'[Amount]);
FILTER('Sales';'Sales'[DocNr] =_max ))
Check out the October 2024 Power BI update to learn about new features.
Learn from experts, get hands-on experience, and win awesome prizes.
User | Count |
---|---|
115 | |
113 | |
105 | |
95 | |
58 |
User | Count |
---|---|
175 | |
147 | |
134 | |
105 | |
82 |