Forum Discussion

joc's avatar
joc
Helper I
9 years ago
Solved

Add new table with calculated lines

Hello everybody,

 

I need your help to create a new table with conditions. I know how to create a new table, but don't what is the formula to calculated what I want :

 

 

I have the first table, and I want the new one. 

 

Thanks a lot for your help,

Joc

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    Hi joc,

     

    You can use below table formula to summary original table records:

    Summary Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Test Table',[Week],"Col 1",SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])&&OR('Test Table'[Name]="A",'Test Table'[Name]="B")),[Value])),
        "Col 2",[Col 1]+[Col 1]/2),
    "Col 3",[Col 1]-[Col 2])

     

     

    Based on your result table, you 'col2' formula should be "A+B+(C+D)/2", right?
    If this is a case, you can use below formula to instead:

    Summary Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Test Table',[Week],"Col 1",SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])&&OR('Test Table'[Name]="A",'Test Table'[Name]="B")),[Value])),
        "Col 2",[Col 1]+(SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])),[Value])-[Col 1])/2),
     "Col 3",[Col 1]-[Col 2])

     

     

    Regards,

    Xiaoxin Sheng

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    Hi joc,

     

    You can use below table formula to summary original table records:

    Summary Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Test Table',[Week],"Col 1",SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])&&OR('Test Table'[Name]="A",'Test Table'[Name]="B")),[Value])),
        "Col 2",[Col 1]+[Col 1]/2),
    "Col 3",[Col 1]-[Col 2])

     

     

    Based on your result table, you 'col2' formula should be "A+B+(C+D)/2", right?
    If this is a case, you can use below formula to instead:

    Summary Table = 
ADDCOLUMNS(
    ADDCOLUMNS(
        SUMMARIZE('Test Table',[Week],"Col 1",SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])&&OR('Test Table'[Name]="A",'Test Table'[Name]="B")),[Value])),
        "Col 2",[Col 1]+(SUMX(FILTER('Test Table',[Week]=EARLIER('Test Table'[Week])),[Value])-[Col 1])/2),
     "Col 3",[Col 1]-[Col 2])

     

     

    Regards,

    Xiaoxin Sheng