Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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
Solved! Go to 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:
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.
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]
any chance this can be achieved in M?
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:
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.
User | Count |
---|---|
122 | |
109 | |
96 | |
59 | |
57 |
User | Count |
---|---|
143 | |
119 | |
102 | |
71 | |
61 |