Calculating total cost of sub-tickets

[Anonymous]

Hello All, 

I want to calculate total cost and total sub-request per request (type). I am attaching xls for reference. I have 3 ticket types, request (main), vendor, and sub-request (sub-request has child/parent and sub-request can have multiple languages or tickets). I am trying to calculate total cost without savings for the request (which would be request X number of sub requests) easy version or If I can take the gross word count from request into the sub-request I could get then calculate accurately weighted vs gross word count (weighted is there as language_quote) and i would need to a way to add gross word from request to subrequest or to vendor and then sub-request. 

Either way the idea is to calculate gross X .11 cents minus total_quote (from request) to get cost savings. 

For the life of me I cannot get this to work. Any help would be greatly appreciated: 

Request to Vendor is found in tab 2 (connections tab; it is seperate table in power bi) 

and sub request has parent ID to the right. 

Thank you so much for your help. 

Issue_Id	Key	Issue_Type_Id	Issue_Type_Name	Gross_Wordcount	Target_Language_Selected	Unit_rate	Fees	Language_Quote	Weighted_wordcount	Total_Quote	Vendor_Quote	Vendor	Parent_Issue_Key	Parent_Issue_Type_Name
342144	LOCAT-29209	10606	Request	200		0	0	0	0	€ 111.43	0
342158	LOCAT-29219	13809	Vendor	0		0	0	0	0	€ 0.00	18.96	osmicka Hong Kong
342157	LOCAT-29218	13809	Vendor	0		0	0	0	0	€ 0.00	6.083	Dimlight
342156	LOCAT-29217	13809	Vendor	0		0	0	0	0	€ 0.00	11.06	Latis Global
342155	LOCAT-29216	13809	Vendor	0		0	0	0	0	€ 0.00	13.035	osmicka Japan
342154	LOCAT-29215	13809	Vendor	0		0	0	0	0	€ 0.00	5.135	Quoted
342153	LOCAT-29214	13809	Vendor	0		0	0	0	0	€ 0.00	5.309	Qloc
342152	LOCAT-29213	13809	Vendor	0		0	0	0	0	€ 0.00	12.166	Local Heroes
342151	LOCAT-29212	13809	Vendor	0		0	0	0	0	€ 0.00	18.128	Seamonkeys
342150	LOCAT-29211	13809	Vendor	0		0	0	0	0	€ 0.00	10.428	Mouse Power
342164	LOCAT-29225	13808	Sub-Request	0	POLISH (PL)	0.064	0.05	5.309	79	€ 0.00	0	Qloc	LOCAT-29214	Vendor
342170	LOCAT-29231	13808	Sub-Request	0	THAI (TH)	0.12	0	9.48	79	€ 0.00	0	osmicka Hong Kong	LOCAT-29219	Vendor
342169	LOCAT-29230	13808	Sub-Request	0	TRADITIONAL CHINESE (TCH)	0.12	0	9.48	79	€ 0.00	0	osmicka Hong Kong	LOCAT-29219	Vendor
342167	LOCAT-29228	13808	Sub-Request	0	KOREAN (KR)	0.14	0	11.06	79	€ 0.00	0	Latis Global	LOCAT-29217	Vendor
342165	LOCAT-29226	13808	Sub-Request	0	PORTUGUESE (BR)	0.065	0	5.135	79	€ 0.00	0	Quoted	LOCAT-29215	Vendor
342168	LOCAT-29229	13808	Sub-Request	0	SIMPLIFIED CHINESE (SCH)	0.07	0.1	6.083	79	€ 0.00	0	Dimlight	LOCAT-29218	Vendor
342166	LOCAT-29227	13808	Sub-Request	0	JAPANESE (JP)	0.15	0.1	13.035	79	€ 0.00	0	osmicka Japan	LOCAT-29216	Vendor
342163	LOCAT-29224	13808	Sub-Request	0	DUTCH (NL)	0.14	0.1	12.166	79	€ 0.00	0	Local Heroes	LOCAT-29213	Vendor
342162	LOCAT-29223	13808	Sub-Request	0	ITALIAN (IT)	0.11	0.1	9.438	78	€ 0.00	0	Seamonkeys	LOCAT-29212	Vendor
342161	LOCAT-29222	13808	Sub-Request	0	SPANISH (ES)	0.1	0.1	8.69	79	€ 0.00	0	Seamonkeys	LOCAT-29212	Vendor
342160	LOCAT-29221	13808	Sub-Request	0	GERMAN (DE)	0.12	0.1	10.428	79	€ 0.00	0	Mouse Power	LOCAT-29211	Vendor
342159	LOCAT-29220	13808	Sub-Request	0	FRENCH (FR)	0.128	0.1	11.123	79	€ 0.00	0	Keywords France	LOCAT-29210	Vendor

[jennratten]

Hello - I have reworded your request below.  Can you please let me know if any part of this is not correct and if so, please revise it accordingly?  

Based on the reworded version below, you can accomplish the calculations in Power Query like so:

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("tZVdb6JAFIb/CvHKJi2ZGb4vWUVBUSng3jS9oDrpmqqzK5qm/36HAZQzNpNw0URBjJ73Yc6Zh5eXgWESbJqDx0G8Gvn5E/EI8vgVRjay+Tml/y60PPNPBCF+5C8kvTHGumk0F83r9bGubLndylhUNlyR8Jset+x0+5tcVpR2da+iYOVht/kotJAd37U5P8gxDohxe8bYOnKrGxjvDvvd+5+zXN0G1Z2+N4F1sZRxcd6V2nTP3oq9nGCBBLtvgqEjw+qs06z4WxzlDNBkbPXMsHQsIp4v7Ey3cm0D1DZ71zbEb5/3bCNXJqCy0XdliI5tsfhsU+y1kJ4YLeUIDCJI/xnFpJq4jBYHdvygX3cBCATgvgFIN0XAgl1KqiXsk56kBBv0lrS9FVSXt6fbJq7qJas4ykJtmMQP1Td8OM36bHVa4XgdgqYvsMENeovggJs0sBIhD/1IG+ZhDSCWvPra001Xzv5u60OhSCC2B0CQGiT1x1EerZZ+rI3CaBlkAeca/QwYkBRxlWDzVRr4S204TxsUs52GxiaARVILdJWMAVxDbCVGskrz9XQtluVX2s6LJTkBDksrCOgamQI8GIinpMiiRRJHkygY35qUtU1CTr1Ag5vHAU9H6vAJIRMByxNHSTTzE7/mmCVNg6wrxlXH385L62aoexkGCJWYSpjxmk+sNlzG3VGpSVr9wWmBLoR2lUGAf4mhBIlyP46qqY3yBgVfUfgWMsQecjskQJlQwTIHkDQhSo6MN0coLsgergD10dXtO731oACSI2rJTYN0US3GOOjapO5Lq3TAAf0OHxgSiAUkR9SSm6TBshqQSesSEd2QYH55t2fm9OuTnbalNjkVxw0FNKhD8/of", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type nullable text) meta [Serialized.Text = true]) in type table [Issue_Id = _t, Key = _t, Issue_Type_Id = _t, Issue_Type_Name = _t, Gross_Wordcount = _t, Target_Language_Selected = _t, Unit_rate = _t, Fees = _t, Language_Quote = _t, Weighted_wordcount = _t, Total_Quote = _t, Vendor_Quote = _t, Vendor = _t, Parent_Issue_Key = _t, Parent_Issue_Type_Name = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Issue_Id", Int64.Type}, {"Key", type text}, {"Issue_Type_Id", Int64.Type}, {"Issue_Type_Name", type text}, {"Gross_Wordcount", Int64.Type}, {"Target_Language_Selected", type text}, {"Unit_rate", type number}, {"Fees", type number}, {"Language_Quote", type number}, {"Weighted_wordcount", Int64.Type}, {"Total_Quote", type number}, {"Vendor_Quote", type number}, {"Vendor", type text}, {"Parent_Issue_Key", type text}, {"Parent_Issue_Type_Name", type text}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Parent Child Level", each if [Issue_Type_Name] = "Request" then 1 else if [Issue_Type_Name] = "Vendor" then 2 else if [Issue_Type_Name] = "Sub-Request" then 3 else -1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ParentIssueKey_Custom", each if [Parent Child Level] = 1 then [Key] else if [Parent_Issue_Key] = "" then null else [Parent_Issue_Key], type text),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"ParentIssueKey_Custom"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down", "Request Key", each if [Issue_Type_Name] = "Request" then [Key] else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Request Key"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down1",{{"Parent_Issue_Key", "Vendor Key"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Request Key", Order.Ascending}, {"Vendor Key", Order.Ascending}, {"Parent Child Level", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Request Key"}, {{"Total_Quote", each List.Sum ( _[Total_Quote] ), Currency.Type }, {"Count of Sub-Requests", each List.Count ( List.Select ( _[Issue_Type_Name], each _ = "Sub-Request" ) ), type number },{"Data", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Gross Cost", each [Total_Quote] * [#"Count of Sub-Requests"], Currency.Type),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Cost Savings", each ( [Gross Cost] * .11 ) - [Total_Quote], Currency.Type)
in
    #"Added Custom4"

To show the word count or any other value from the request on the rows for the vendor or sub-request, if the corresponding cell values for the vendors or sub-requests are null instead of blank, then you can simply fill-down the cells and they will be populated.  This is demonstrated in the "Filled Down" step in the sample query pasted above.

There are three values in the issue types column:

• Request
  • Requests are parents of vendors.
  • In the sample data table provided, there is no key which identifies the relationship between the request and vendor records, but there is only one request present and all vendor records are it's children and will appear in rows directly underneath the request.
• Vendor
  • Vendors are children of requests and parents of sub-requests.
• Sub-request
  • Sub-requests are children of vendors and can have sibling relationships with other sub-requests.
  • The parent issue key is in a column to the right

I want to calculate:

• number of sub-requests per request
• gross cost for the request
  • This is the total cost without any savings for the request and is calculated by multiplying the total quote amount for the request by the number of sub-requests.
• cost savings
  • This is calculated by multiplying the gross cost for the request times .11 cents minus the total_quote for the request. 

I would also like to show:

• The gross word count from request on the row for each sub-request.

[Anonymous]

Hello @jennratten , 

You are a genius 🙂 I have one question, how do I insert this or copy this into powe bi? One at a time or through power query? 

2. I stated the math a bit off:

I want to calculate:

• number of sub-requests per request (correct) 🙂 This is my main problem on how to add a column w/ this information into the table view
• gross cost for the request
  • This is the total cost without any savings for the request and is calculated by multiplying the gross word count request by the number of sub-requests and multiplying by .11. in Essence it is total cost = (gross word count * # of sub-requests * .11$) 
• cost savings
  • This is calculated by gross cost - total quote 

I would also like to show: (with what you have added this is not requred 🙂 ) 

• The gross word count from request on the row for each sub-request.

You are amazing, i have no idea how you did this, if you can let me know how to add this to power bi 🙂 Thank you sooo soo much. 

Lukas 

[jennratten]

Hi @Anonymous - apologies for the delayed response.  You are so very welcome!!  To apply this in Power Query, beginning with the table which looks like what was posted in the original message, right-click on the table name and create a reference table.  Then open up the Advanced Editor for that new query and replace the contents with the script below, just change #"Your Table Name Goes Here" to your actual query name being referenced.  

I have also included the changes requested above.  Please have a look!

If this has solved your challenge please consider clicking Accept as Solution and giving it a kudo.  

let
Source = #"Your Table Name Goes Here",
    #"Added Custom" = Table.AddColumn(Source, "Parent Child Level", each if [Issue_Type_Name] = "Request" then 1 else if [Issue_Type_Name] = "Vendor" then 2 else if [Issue_Type_Name] = "Sub-Request" then 3 else -1, Int64.Type),
    #"Added Custom1" = Table.AddColumn(#"Added Custom", "ParentIssueKey_Custom", each if [Parent Child Level] = 1 then [Key] else if [Parent_Issue_Key] = "" then null else [Parent_Issue_Key], type text),
    #"Filled Down" = Table.FillDown(#"Added Custom1",{"ParentIssueKey_Custom"}),
    #"Added Custom3" = Table.AddColumn(#"Filled Down", "Request Key", each if [Issue_Type_Name] = "Request" then [Key] else null, type text),
    #"Filled Down1" = Table.FillDown(#"Added Custom3",{"Request Key"}),
    #"Renamed Columns" = Table.RenameColumns(#"Filled Down1",{{"Parent_Issue_Key", "Vendor Key"}}),
    #"Sorted Rows" = Table.Sort(#"Renamed Columns",{{"Request Key", Order.Ascending}, {"Vendor Key", Order.Ascending}, {"Parent Child Level", Order.Ascending}}),
    #"Grouped Rows" = Table.Group(#"Sorted Rows", {"Request Key"}, {{"Total_Quote", each List.Sum ( _[Total_Quote] ), Currency.Type }, {"Gross_Wordcount", each List.Sum ( _[Gross_Wordcount] ), Int64.Type }, {"Count of Sub-Requests", each List.Count ( List.Select ( _[Issue_Type_Name], each _ = "Sub-Request" ) ), type number },{"Data", each _, type table}}),
    #"Added Custom2" = Table.AddColumn(#"Grouped Rows", "Gross Cost", each [Gross_Wordcount] * [#"Count of Sub-Requests"] * 0.11, Currency.Type),
    #"Added Custom4" = Table.AddColumn(#"Added Custom2", "Cost Savings", each [Gross Cost] - [Total_Quote], Currency.Type)
in
    #"Added Custom4"