Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

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

MonthKPI_codeKPI_nameActual value
1P0002Revenue100
1P0020Gross Profit80
1P0027R&D costs10
1P0034S&M costs20
1P0041G&A costs10
1P0046Employee costs10
1P0050EBITDA30

 

Table2: New table (employee costs allocated to R&D, S&M and G&A costs) 

MonthKPI_codeKPI_nameActual value
1P0002Revenue100
1P0020Gross Profit80
1P0027R&D costs13
1P0034S&M costs25
1P0041G&A costs12
1P0050EBITDA30

 

Table3: Share (table specifying the allocation of employee costs for each month summing to 100%)

 

MonthKPI_codeKPI_nameEmployee costs allocation
1P0027R&D costs30%
1P0034S&M costs50%
1P0041G&A costs20%

 

How do I create the new table using DAX/Power Query?

 

Thanks in advance!

1 ACCEPTED SOLUTION
AlB
Super User
Super User

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"

 

 

SU18_powerbi_badge

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.

 

View solution in original post

5 REPLIES 5
v-alq-msft
Community Support
Community Support

Hi, @Anonymous 

 

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

Old:

c1.png

 

Share:

c2.png

 

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:

c3.png

 

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
Not applicable

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?

Anonymous
Not applicable

 

 

 

let
    Origine = Table.Combine({Table1, Table3}),
    #"Raggruppate righe" = Table.Group(Origine,  {"Month", "KPI_code", "KPI_name"}, {{"Actual value", each  _[Actual value]{0}*(1+ (try  [Employee costs allocation]{1} otherwise 0))}})
in
    #"Raggruppate righe"

 

 

 

AlB
Super User
Super User

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"

 

 

SU18_powerbi_badge

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.

 

Anonymous
Not applicable

This one did the trick! Thanks!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors