cancel
Showing results for
Did you mean:
Helper I

## Sum of values based off of record ID

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:

SUM OF ALL back order items TOTAL COST linked to parent work order item record ID =
CALCULATE(
SUM( 'work order items'[total cost]),
FILTER(
ALL( 'work order items'),
'work order items'[work order back order item record ID] = 'invoice line'[work order item record id]
)
)

This returns 40,113.62.

I need it to only calculate the sum of the first occurrence of the record ID in the work order table based on the invoice item it's linked to so in bold I want it to calculate the sum of the following:

 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!

7 REPLIES 7
Super User II

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
Not applicable

@mvwd  - 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])``````
I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.
Helper I

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
Not applicable

@mvwd  - 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
Not applicable

@mvwd - Which formula are you referring to? Did you try both of them?

Helper I

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!

Super User II

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]
)
)``````

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

#### Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

#### Microsoft Dynamics 365 & Power Platform User Professionals

DynamicsCon is a FREE, 4 half-day virtual learning experience for 11,000+ Microsoft Business Application users and professionals.

Top Solution Authors
Top Kudoed Authors