cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
mvwd
Helper I
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 idcost persell perqtytotal costtotal sellwork order back order item record ID work order #work order main sales repsplit #sales rep splitSplit %age
2810853950.01101468.7153950.01101468.7 5sales rep 11180sales rep 10.8 
2810853950.01101468.7153950.01101468.7 5sales rep 11180sales rep 20.2 
300023890.3013890.30281085sales rep 11180sales rep 10.8 
300023890.3013890.30281085sales rep 11180sales rep 20.2 
2984019.220119.220281085sales rep 11180sales rep 10.8 
2984019.220119.220281085sales rep 11180sales rep 20.2 
2816316147.290116147.290281085sales rep 11180sales rep 10.8 
2816316147.290116147.290281085sales rep 11180sales rep 20.2 
             
invoice line record ID          
record IDwork order item record idsell perqtytotal sellwork order item parent amount (linked from work order item record ID)Percentage of item invoicedwork 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 IDinvoice line total cost = parent item total cost + cost of all sub back order itemssplit #sales rep splitSplit %age
2665828108101468.71101468.7101468.7153950.0140113.6294063.321180sales rep 10.8
2665828108101468.71101468.7101468.7153950.0140113.6294063.321180sales rep 20.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 idcost persell perqtytotal costtotal sellwork order back order item record ID work order #work order main sales repsplit #sales rep splitSplit %age
2810853950.01101468.7153950.01101468.7 5sales rep 11180sales rep 10.8
2810853950.01101468.7153950.01101468.7 5sales rep 11180sales rep 20.2
300023890.3013890.30281085sales rep 11180sales rep 10.8
300023890.3013890.30281085sales rep 11180sales rep 20.2
2984019.220119.220281085sales rep 11180sales rep 10.8
2984019.220119.220281085sales rep 11180sales rep 20.2
2816316147.290116147.290281085sales rep 11180sales rep 10.8
2816316147.290116147.290281085sales rep 11180sales rep 20.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
jdbuchanan71
Super User II
Super User II

@mvwd 

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.
Nathan Peterson

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         
Indedrecord idtotal costtotal sellwork order back order item record ID work order #work order main sales repsplit #sales rep splitSplit %age
12810853950.01101468.69 5sales rep 11180sales rep 10.8 
22810853950.01101468.69 5sales rep 11180sales rep 20.2 
3300023890.30281085sales rep 11180sales rep 10.8 
4300023890.30281085sales rep 11180sales rep 20.2 
52984019.220281085sales rep 11180sales rep 10.8 
62984019.220281085sales rep 11180sales rep 20.2 
72816316147.290281085sales rep 11180sales rep 10.8 
82816316147.290281085sales rep 11180sales rep 20.2 
           
invoice line        
record IDwork order item record idtotal sellwork order item parent amount (linked from work order item record ID)Percentage of item invoicedwork 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 itemssplit #sales rep splitSplit %age
2665828108101468.69101468.69100%53950.0140113.6294063.321180sales rep 10.8
2665828108101468.69101468.69100%53950.0140113.6294063.321180sales rep 20.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        
Indedrecord idtotal costtotal sellwork order back order item record ID work order #work order main sales repsplit #sales rep splitSplit %age
12810853950.01101468.69 5sales rep 11180sales rep 10.8
22810853950.01101468.69 5sales rep 11180sales rep 20.2
3300023890.30281085sales rep 11180sales rep 10.8
4300023890.30281085sales rep 11180sales rep 20.2
52984019.220281085sales rep 11180sales rep 10.8
62984019.220281085sales rep 11180sales rep 20.2
72816316147.290281085sales rep 11180sales rep 10.8
82816316147.290281085sales rep 11180sales rep 20.2

 

in the invoice line item table it would look like:

record IDwork order item record idtotal sellwork order item parent amount (linked from work order item record ID)Percentage of item invoicedwork 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 itemssplit #sales rep splitSplit %age
2665828108101468.69101468.69100%53950.0120056.8194063.321180sales rep 10.8
2665828108101468.69101468.69100%53950.0120056.8194063.321180sales rep 20.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?

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!

@mvwd 

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

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

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.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

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

R2 (Green) 768 x 460px.png

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.