Store SUM from table 1 in table 2

[r_z]

Hello smart people.

I am new to Power Bi, DAX and I need some help.

I have table 1 in Power Bi where I have list of different part numbers with current stock levels. I am then projecting closing stock values for each week.

Question 1: How to create Table 2 in Power BI using DAX and store total closing stock from Table 1 for each week ?

Question 2:  Is it possible to store result of calculation from table 1 as values in table 2, so as soon as week finished, numbers for that week are captured and they don't change everytime I press "Refresh". To do this in excel I would take cell with formula and copy/paste as values.  

I am able to do this in Excel, but I want to do this in Power BI, so I don't need Excel (middle man) to do the calculation. 

Table 1
Part Number	Current Stock	Week 1 Closing stock	Week 2 Closing stock	Week 3 Closing stock	Week 4 Closing stock	etc -->
A123	10	15	20	18	16
B123	10	15	20	18	16
C123	10	15	20	18	16
D123	10	15	20	18	16
E123	10	15	20	18	16
etc
Table 2
Weeks	Total Closing stock
Week 1	75
Week 2	100
Week 3	90
Week 4	80

Massive thanks.

R

[ryan_mayu]

Q1:  Edit queries and unpivot columns (in the transform tab). That can switch columns with rows.

Q2: build relationship between two tables, then you can use calculate(sum(amount), week="week1").  I am also a new users and hope that can help you.

[r_z]

Hi Ryan

Thanks for reply.

Q1: For table 1 I have database connection with base data, I then adding DAX calculation columns to do projection and these columns not appearing in Power Querie. It looks like that I have to use either DAX or Power Querie, but not both at the same time.

Q2: calculate(sum(amount), week="week1") - this would sum totals only for Week 1, but how to "tell table 2" that for Week 1 it has to take sum from Week 1 (Table 1), for Week 2 sum from Week 2 (Table 1), for Week 3 sum from Week 3 (Table 1) etc... and all this happens in the same column.

[ryan_mayu]

Could you please provide me your email address? I create a simple sample for you. Hope that will be helpful.

[r_z]

Hi Ryan

My email randomski.social@gmail.com

Thanks for help.

R

[r_z]

Hi All

Thanks for your help so far.

Please see linked Excel spreadsheet with explanation, what I am trying to achieve.

Thanks

R_Z

[ryan_mayu]

Q1: I am not sure what kind of DAX you added, but if you add some calculated column, you may not be able to unpivot columns.

Q2: What I can think about is you try to create another new table. One way is "new table = old table". The other way is to right clik and create a reference table. Then you use group by function, group by week column and sum by amount column. Then you will get the total amount for week 1, week2, week3....

Hope that is helpful

[r_z]

bump

[PattemManohar]

Please try this.....

let
	Source = Table.FromRows(Json.Document(Binary.Decompress(Binary.FromText("i45WcjQ0MlbSUTI0ABGmQMIIzLIAEWYgrlKsTrSSE1GqnIlS5UKUKlfCqmIB", BinaryEncoding.Base64), Compression.Deflate)), let _t = ((type text) meta [Serialized.Text = true]) in type table [#"Part Number" = _t, #"Current Stock" = _t, #"Week 1 Closing stock" = _t, #"Week 2 Closing stock" = _t, #"Week 3 Closing stock" = _t, #"Week 4 Closing stock" = _t, #"Week 5 Closing stock" = _t]),
	#"Removed Columns" = Table.RemoveColumns(Source,{"Week 5 Closing stock"}),
	#"Changed Type" = Table.TransformColumnTypes(#"Removed Columns",{{"Current Stock", Int64.Type}, {"Week 1 Closing stock", Int64.Type}, {"Week 2 Closing stock", Int64.Type}, {"Week 3 Closing stock", Int64.Type}, {"Week 4 Closing stock", Int64.Type}}),
	#"Removed Columns1" = Table.RemoveColumns(#"Changed Type",{"Part Number", "Current Stock"}),
	#"Demoted Headers" = Table.DemoteHeaders(#"Removed Columns1"),
	#"Changed Type1" = Table.TransformColumnTypes(#"Demoted Headers",{{"Column1", type any}, {"Column2", type any}, {"Column3", type any}, {"Column4", type any}}),
	#"Transposed Table" = Table.Transpose(#"Changed Type1"),
	#"Added Custom" = Table.AddColumn(#"Transposed Table", "Total Closing Stock", each [Column2]+[Column3]+[Column4]+[Column5]+[Column6]),
	#"Removed Columns2" = Table.RemoveColumns(#"Added Custom",{"Column2", "Column3", "Column4", "Column5", "Column6"}),
	#"Inserted Text Before Delimiter" = Table.AddColumn(#"Removed Columns2", "Text Before Delimiter", each Text.BeforeDelimiter([Column1], " ", 1), type text),
	#"Reordered Columns" = Table.ReorderColumns(#"Inserted Text Before Delimiter",{"Column1", "Text Before Delimiter", "Total Closing Stock"}),
	#"Removed Columns3" = Table.RemoveColumns(#"Reordered Columns",{"Column1"}),
	#"Renamed Columns" = Table.RenameColumns(#"Removed Columns3",{{"Text Before Delimiter", "Weeks"}})
in
	#"Renamed Columns"

[r_z]

Hi pattemmanohar

Where to put this "code" ?

Looks wierd...

Thanks

R

[Anonymous]

@r_z

The code that @PattemManohar provided you is Power Query code (aka M). Within Power BI Desktop click on 'Edit Queries'. From the resulting page click on 'Advanced Editor'. This will bring up a window where you should place the code provided.