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 have two tables, work order items and invoice items.
This is a condensed table on how the data looks:
work order items | ||||||||||||
record id | cost per | sell per | qty | total cost | total sell | work order back order item record ID | work order # | work order main sales rep | split # | sales rep split | Split %age | |
28108 | 53950.01 | 101468.7 | 1 | 53950.01 | 101468.7 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | ||
28108 | 53950.01 | 101468.7 | 1 | 53950.01 | 101468.7 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | ||
30002 | 3890.3 | 0 | 1 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
30002 | 3890.3 | 0 | 1 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
29840 | 19.22 | 0 | 1 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
29840 | 19.22 | 0 | 1 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
28163 | 16147.29 | 0 | 1 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
28163 | 16147.29 | 0 | 1 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
invoice line record ID | ||||||||||||
record ID | work order item record id | sell per | qty | total sell | work order item parent amount (linked from work order item record ID) | Percentage of item invoiced | work order item parent total cost (linked from work order item record ID) | SUM OF ALL back order items TOTAL COST linked to parent work order item record ID | invoice line total cost = parent item total cost + cost of all sub back order items | split # | sales rep split | Split %age |
26658 | 28108 | 101468.7 | 1 | 101468.7 | 101468.7 | 1 | 53950.01 | 40113.62 | 94063.32 | 1180 | sales rep 1 | 0.8 |
26658 | 28108 | 101468.7 | 1 | 101468.7 | 101468.7 | 1 | 53950.01 | 40113.62 | 94063.32 | 1180 | sales rep 2 | 0.2 |
The first two columns are unique record ID's but it doubles because we can split the sales between more then one rep.
basically since there is no cost for invoice items in our system we link each invoice item to a work order item via the work order record ID.
My problem is that my DAX formula for "SUM OF ALL back order items TOTAL COST linked to parent work order item record ID" is doubling. in the 2nd table above it shows as 40,113.62 and I need it to show as 20056.81. This is my dax formula to get this sum:
record id | cost per | sell per | qty | total cost | total sell | work order back order item record ID | work order # | work order main sales rep | split # | sales rep split | Split %age |
28108 | 53950.01 | 101468.7 | 1 | 53950.01 | 101468.7 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
28108 | 53950.01 | 101468.7 | 1 | 53950.01 | 101468.7 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
30002 | 3890.3 | 0 | 1 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
30002 | 3890.3 | 0 | 1 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
29840 | 19.22 | 0 | 1 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
29840 | 19.22 | 0 | 1 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
28163 | 16147.29 | 0 | 1 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
28163 | 16147.29 | 0 | 1 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
the total should be 20056.81.
I hope this makes sense and I don't want to just divide by 2 because the sale could be split 3 ways and then the data would be in there 3 times.
Thanks!
@Anonymous
If you write a measure to apply the split % then the sum should give you the correct amount. Something like this.
Amount Split = SUMX ( 'work order items','work order items'[total cost]*'work order items'[Split %age])
@Anonymous - You said you want the first, but it appears they all have the same value - so the real issue is you want DISTINCT values. You can do something like this:
SUM OF ALL back order items TOTAL COST linked to parent work order item record ID =
var _work_order_id = MAX('invoice line'[work order item record id])
var _table = DISTINCT(
SELECTCOLUMNS(
FILTER(ALL('work order items'), [work order item record id] = _work_order_id ),
"id", [record id],
"cost", [total cost]
)
)
return SUMX(_table, [cost])
thanks for the formula. I'm still new to power bi and i'm not able to get it to work...
I added an index column to the work order item table and not sure if I was clear above
so now it looks like this:
work order items | ||||||||||
Inded | record id | total cost | total sell | work order back order item record ID | work order # | work order main sales rep | split # | sales rep split | Split %age | |
1 | 28108 | 53950.01 | 101468.69 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | ||
2 | 28108 | 53950.01 | 101468.69 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | ||
3 | 30002 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
4 | 30002 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
5 | 29840 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
6 | 29840 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
7 | 28163 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
8 | 28163 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
invoice line | ||||||||||
record ID | work order item record id | total sell | work order item parent amount (linked from work order item record ID) | Percentage of item invoiced | work order item parent total cost (linked from work order item record ID) | SUM OF ALL back order items TOTAL COST linked to work order back order item record ID | invoice line total cost = parent item total cost + cost of all sub back order items | split # | sales rep split | Split %age |
26658 | 28108 | 101468.69 | 101468.69 | 100% | 53950.01 | 40113.62 | 94063.32 | 1180 | sales rep 1 | 0.8 |
26658 | 28108 | 101468.69 | 101468.69 | 100% | 53950.01 | 40113.62 | 94063.32 | 1180 | sales rep 2 | 0.2 |
not sure if i was clear in my 1st question above.
first in the work order items table do you see how items with index 3 - 8 have the record ID of index 1-2 in the field "work order back order item record ID " this just means it's linked to that item to add into the cost later.
I need a column in the invoice table called "SUM OF ALL back order items TOTAL COST linked to parent work order item record ID"
I want this column to be calculated as the following
Get the sum of the "total cost" field in the work order items table for all items where the 'work order items'[work order back order item record ID] = 'invoice line'[work order item record ID].
HOWEVER I only want to pull sum the total cost of the work order items table of the first row where there is a new 'work order items'[Record ID] AND 'work order items'[work order back order item record ID]
my ideal data would look like this
GET SUM of the total cost OF ALL ITEMS HIGHLIGHTED IN RED
work order items | |||||||||
Inded | record id | total cost | total sell | work order back order item record ID | work order # | work order main sales rep | split # | sales rep split | Split %age |
1 | 28108 | 53950.01 | 101468.69 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 | |
2 | 28108 | 53950.01 | 101468.69 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 | |
3 | 30002 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
4 | 30002 | 3890.3 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
5 | 29840 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
6 | 29840 | 19.22 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
7 | 28163 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 1 | 0.8 |
8 | 28163 | 16147.29 | 0 | 28108 | 5 | sales rep 1 | 1180 | sales rep 2 | 0.2 |
in the invoice line item table it would look like:
record ID | work order item record id | total sell | work order item parent amount (linked from work order item record ID) | Percentage of item invoiced | work order item parent total cost (linked from work order item record ID) | SUM OF ALL back order items TOTAL COST linked to work order back order item record ID | invoice line total cost = parent item total cost + cost of all sub back order items | split # | sales rep split | Split %age |
26658 | 28108 | 101468.69 | 101468.69 | 100% | 53950.01 | 20056.81 | 94063.32 | 1180 | sales rep 1 | 0.8 |
26658 | 28108 | 101468.69 | 101468.69 | 100% | 53950.01 | 20056.81 | 94063.32 | 1180 | sales rep 2 | 0.2 |
is this what your formula does?
Thanks,
@Anonymous - I think the issue may be that you are thinking of a Calculated Column, while @jdbuchanan71 and I were both thinking of a Measure. Could you try it as a measure?
Other than that, we may need to work with your pbix file to diagnose the issue.
Hope this helps,
Nathan
@Anonymous - Which formula are you referring to? Did you try both of them?
I tried both of them and it didn't get the results I wanted so I was just asking you if your formula provided to green value per the table!
@Anonymous
For mine you would add my measure first then modify your measure to use the new one.
Amount Split = SUMX ( 'work order items','work order items'[total cost]*'work order items'[Split %age])
SUM OF ALL back order items TOTAL COST linked to parent work order item record ID =
CALCULATE(
[Amount Split],
FILTER(
ALL( 'work order items'),
'work order items'[work order back order item record ID] = 'invoice line'[work order item record id]
)
)
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 |
---|---|
112 | |
100 | |
80 | |
64 | |
57 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |