Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello,
I got a simple task that I can't seem to get my head around.
I have a table with following data:
Order no | Part No | Component part | QtyOnOrder | DateDue |
1000 | 123 | 234 | 100 | 19.3.2020 |
1001 | 123 | 234 | 200 | 20.3.2020 |
1001 | 123 | 567 | 200 | 20.3.2020 |
And I have another table with just the Part No. In that table I want to get the Qty per group, so in this case
123 = 100 + 200
I tried using SUMMARIZE, but I already have +30 tables in my dataset and don't want to clutter it any extra.
The tables are related. And if there's a better way to get the qty of the distinct order no to the same product, please suggest so.
So order no 1001 will create 200 pcs 123, but use 234 and 567 to be created. And I need to be able to filter on the date (which is related to the date table).
Thanks in advance!
EDIT: because I need the date field, the best idea is probably to go with a measure that can calculate the average of qty on order based on the group function of order no. Is that possible?
@Anonymous , New column in your part no table
New column = Sumx(filter(Table, Table[Part No] = part[Part no]),Table[QtyOnOrder])
Would work. But the column QtyOnOrder contains "duplicates" which is based on the order no, so the result with your expression would lead to 500 (instead of the wanted 300).
Thanks!
Found this:
Measure:
TotalOrderAmount = SUMX(DISTINCT(Table1[Order no]),CALCULATE(AVERAGE(Table1[qty])))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
115 | |
100 | |
88 | |
68 | |
61 |
User | Count |
---|---|
150 | |
120 | |
100 | |
87 | |
68 |