Additional column distributing a value based on the sum of another column

[sperry]

I have a table of grants made to organisations. I need to distributed the grant amount across different locations based on the population.

So in the example model below for Grant_ID 31074 for and for Value = Bowden I need to distribute $45,000 across the sum pop2018 multiplied by 660. I have included a link to a test model. 

The end game is to get a $ amount for each area which I can do in a measure once I have the Additional Column in the query editor

Test For Distribution 

[jdbuchanan71]

Hello @sperry 

This measure should give you the column you are looking for.

Allocated Funding = 
VAR _ProjAmount = CALCULATE ( SUM ( Table1[CAUPopProj.2018] ), ALLEXCEPT ( Table1,Table1[Grant ID] ) )
RETURN DIVIDE ( Table1[CAUPopProj.2018], _ProjAmount ) * Table1[Funding Amount]

[sperry]

any chance this can be achieved in M?

[v-joesh-msft · Accepted solution]

Hi @sperry ,

You can try the following M-query, the "Source" is in my query and starting with "Changed Type", is in yours:

let
    Source = Excel.Workbook(File.Contents("C:\Users\joeysh\1108\New Microsoft Excel Worksheet.xlsx"), null, true),
    Table1_Table = Source{[Item="Table1",Kind="Table"]}[Data],
    #"Changed Type" = Table.TransformColumnTypes(Table1_Table,{{"Grant ID", type text}, {"Funding Amount", Int64.Type}, {"Attribute", type text}, {"Value", type text}, {"CAUPopProj.2018", Int64.Type}}),
    #"Grouped Rows" = Table.Group(#"Changed Type", {"Grant ID"}, {{"all_id_sales", each List.Sum([CAUPopProj.2018]), type number}, {"all", each _, type table [Grant ID=text, Funding Amount=number, Attribute=text, Value=text, CAUPopProj.2018=number]}}),
    #"Expanded all" = Table.ExpandTableColumn(#"Grouped Rows", "all", {"Grant ID", "Funding Amount", "Attribute", "Value", "CAUPopProj.2018"}, {"Grant ID.1", "Funding Amount", "Attribute", "Value", "CAUPopProj.2018"}),
    #"Added Custom" = Table.AddColumn(#"Expanded all", "Custom", each ([CAUPopProj.2018]/[all_id_sales])*[Funding Amount]),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"all_id_sales", "Grant ID.1"})
in
    #"Removed Columns"

Here is a demo, please try it:

https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EcIxjerijxJMvR0DYU...

Best Regards,

Community Support Team _ Joey
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.