Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

Creating a new table summarizing rows from other tables

I have looked around for potential solutions, but I cannot find something that applies specifically to my problem. I have tried using CALCULATE(SUM) with different FILTERs to do a quick fix, but it d...
Need Help
  • AlB's avatar
    AlB
    5 years ago

    Hi Anonymous 

    This is the M code for creating Table2. See it all at work in the attached file.

     

    let
    Source = Table.NestedJoin(Table1, {"Month", "KPI_code"}, Table3, {"Month", "KPI_code"}, "Table3", JoinKind.LeftOuter),
    #"Expanded Table3" = Table.ExpandTableColumn(Source, "Table3", {"Employee costs allocation"}, {"Employee costs allocation"}),
    #"Added Custom" = Table.AddColumn(#"Expanded Table3", "Added employee costs", each [Actual value] + (if [Employee costs allocation] = null then 0 else [Employee costs allocation])*Table.SelectRows(#"Expanded Table3", (inner)=>inner[Month]=[Month] and inner[KPI_code]="P0046")[Actual value]{0}, type number),
    #"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Actual value", "Employee costs allocation"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"Added employee costs", "Actual value"}})
in
    #"Renamed Columns"

     

     

    		 Please accept the solution when done and consider giving a thumbs up if posts are helpful. 

    Contact me privately for support with any larger-scale BI needs, tutoring, etc.

     

v-alq-msft's avatar
v-alq-msft
Community Support
5 years ago

Hi, Anonymous 

 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Old:

 

Share:

 

You may create a new query with the following m codes in 'Advanced Editor'.

let
    Source = Table.AddColumn( Table.SelectRows(Old,each [KPI_code]<>"P0046"),"Result",each 
if List.Contains(Share[KPI_name],[KPI_name])
then [Actual value]+ List.Sum(Table.SelectRows(Old,each [KPI_code]="P0046")[Actual value])* List.Sum( Table.SelectRows(Share,(x)=>x[KPI_name]=[KPI_name])[Employee costs allocation])
else [Actual value]
)
in
    Source

 

Result:

 

Best Regards

Allan

 

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

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Hi all

 

Thank you very much for your suggested solutions. I believe I am very close to getting one of them to work, but I simply can't get the right values.

 

I am encountering two problems, either:

1) The solution does not account for each of the row having a date - the new rows should only take values from old rows with the same corresponding date

2) The solution does not account for other rows being present. The three rows I showed "R&D", "S&M" and "G&A" are oversimplified, so there are 30 other rows from the income statement that do not need to be multiplied (and are not mentioned in Table3).

Am I missing something in one of the solutions here? The relationships between the tables I have made as dates, is that correct?