Forum Discussion
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 didn't do the trick (and of course did not create a new table).
I want to create a divisional income statement, but I have difficulties creating a new table with "employee costs" allocated to divisional areas based on the old table from our ERP system.
Table1: Old table from ERP system
| Month | KPI_code | KPI_name | Actual value |
| 1 | P0002 | Revenue | 100 |
| 1 | P0020 | Gross Profit | 80 |
| 1 | P0027 | R&D costs | 10 |
| 1 | P0034 | S&M costs | 20 |
| 1 | P0041 | G&A costs | 10 |
| 1 | P0046 | Employee costs | 10 |
| 1 | P0050 | EBITDA | 30 |
Table2: New table (employee costs allocated to R&D, S&M and G&A costs)
| Month | KPI_code | KPI_name | Actual value |
| 1 | P0002 | Revenue | 100 |
| 1 | P0020 | Gross Profit | 80 |
| 1 | P0027 | R&D costs | 13 |
| 1 | P0034 | S&M costs | 25 |
| 1 | P0041 | G&A costs | 12 |
| 1 | P0050 | EBITDA | 30 |
Table3: Share (table specifying the allocation of employee costs for each month summing to 100%)
| Month | KPI_code | KPI_name | Employee costs allocation |
| 1 | P0027 | R&D costs | 30% |
| 1 | P0034 | S&M costs | 50% |
| 1 | P0041 | G&A costs | 20% |
How do I create the new table using DAX/Power Query?
Thanks in advance!
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.
5 Replies
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.
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 SourceResult:
Best Regards
Allan
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
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?
