Sum of values based off of record ID

[Anonymous]

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!

[jdbuchanan71]

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

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

I hope this helps. If it does, please Mark as a solution.
I also appreciate Kudos.

[Anonymous]

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]

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

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

[Anonymous]

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!

[jdbuchanan71]

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