Forum Discussion

r_z's avatar
r_z
Frequent Visitor
7 years ago

Store SUM from table 1 in table 2

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 NumberCurrent StockWeek 1 Closing stockWeek 2 Closing stockWeek 3 Closing stockWeek 4 Closing stocketc -->
A1231015201816 
B1231015201816 
C1231015201816 
D1231015201816 
E1231015201816 
etc       
       
       
       
       
       
Table 2      
       
WeeksTotal Closing stock     
Week 175     
Week 2100     
Week 390     
Week 480     
       
       

 

 

 

Massive thanks.

R

10 Replies

  • ryan_mayu's avatar
    ryan_mayu
    Icon for Super User rankSuper User
    7 years ago

    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's avatar
      r_z
      Frequent Visitor
      7 years ago

      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.