Calculate percentage and add to total

[Anonymous]

Hi,

I'm struggling to work out how to achieve what I want in PowerBI. I have a table of customers (Table1), with a 'Shared' row.

What I want to do is take the value of 'Shared' from Table1, and add the value to each customer row, based upon the precentage distribuion in Table2. Is such a thing possible?

E.g. below, Customer A would become '280' and Customer B would become '220'

Table1

Customer A	100
Customer B	200
Shared	300

Table2

Customer A	60%
Customer B	40%

[Mariusz]

Hi @Anonymous 

Please see the attached file with a solution in Power Query or the script below

let
    Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45Wci4tLsnPTS1ScFTSUTI0MFCK1UESdAIKGkEFgzMSi1JTgALGIIFYAA==", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [Customer = _t, Value = _t]),
    #"Changed Type" = Table.TransformColumnTypes(Source,{{"Customer", type text}, {"Value", Int64.Type}}),
    #"Merged Queries" = Table.NestedJoin(#"Changed Type", {"Customer"}, Table2, {"Shared"}, "Table2", JoinKind.LeftOuter),
    #"Expanded Table2" = Table.ExpandTableColumn(#"Merged Queries", "Table2", {"Customer", "Split"}, {"Table2.Customer", "Table2.Split"}),
    #"Replaced Value" = Table.ReplaceValue(#"Expanded Table2",null,1,Replacer.ReplaceValue,{"Table2.Split"}),
    #"Added Conditional Column" = Table.AddColumn(#"Replaced Value", "New Customer", each if [Table2.Customer] = null then [Customer] else [Table2.Customer], type text ),
    #"Added Custom" = Table.AddColumn(#"Added Conditional Column", "New Value", each [Value] * [Table2.Split], type number ),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"New Customer"}, {{"New Value ", each List.Sum([New Value]), type number}})
in
    #"Grouped Rows"

Best Regards,
Mariusz

If this post helps, then please consider Accepting it as the solution.

Please feel free to connect with me.
LinkedIn

[az38 · Accepted solution]

Hi @Anonymous 

try a column like

Column = 
var _shared = MAXX(FILTER(Table1, Table1[Customer] = "Shared"), Table1[Value])
RETURN
RELATED(Table1[Value]) + Table2[Percent]*_shared

 but make sure you have relationships between tables