Forum Discussion

joc's avatar
joc
Helper I
9 years ago
Solved

New table with calculated lines

Hello everybody,

 

I have this formula for this table (TABLE1) and it's work :

 

SUM1 = 
ADDCOLUMNS(
    ADDCOLUMNS(
	ADDCOLUMNS(
        SUMMARIZE('TABLE1';[WEEK];"Col 1";SUMX(FILTER('TABLE1';[WEEK]=EARLIER('TABLE1'[WEEK])&&('TABLE1'[NAME]="A"||'TABLE1'[NAME]="B"));[VAL]));
        "Col 2";SUMX(FILTER('TABLE1';[WEEK]=EARLIER('TABLE1'[WEEK])&&('TABLE1'[NAME]="C"||'TABLE1'[NAME]="D"));[VAL]));
	"Col 3";[Col 1]+[Col 2]/2);
        "Col 4";[Col 1]-[Col 3])

TABLE1 and SUM1

 

 

 

but I have an other table (TABLE2), with the sames values (TYPE = good) and other values (TYPE = bad)

 

I don't where modify my formula to have only (TYPE = good) ????

 

 TABLE2

 

 

 

 

Thanks a lot for your help,

Joc

Need Help
  • MFelix's avatar
    MFelix
    9 years ago

    Hi joc,

     

    Just add the Type = good to your filter statement:

     

    SUM2 =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                'TABLE2',
                [WEEK],
                "Col 1", SUMX (
                    FILTER (
                        'TABLE2',
                        [WEEK] = EARLIER ( 'TABLE2'[WEEK] )
                            && ( 'TABLE2'[NAME] = "A"
                            || 'TABLE2'[NAME] = "B" )
                            && Table2[TYPE] = "good"
                    ),
                    [VAL]
                )
            ),
            "Col 2", SUMX (
                FILTER (
                    'TABLE2',
                    [WEEK] = EARLIER ( 'TABLE2'[WEEK] )
                        && ( 'TABLE2'[NAME] = "C"
                        || 'Table2'[NAME] = "D" )
                        && Table2[TYPE] = "good"
                ),
                [VAL]
            )
        ),
        "Col 3", [Col 1]
            + [Col 2] / 2
    ),
    "Col 4", [Col 1] - [Col 3]
)

     

    Regards,

    MFelix

  • v-chuncz-msft's avatar
    v-chuncz-msft
    9 years ago

    joc,

     

    You could also refer to the following DAX.

    Table =
ADDCOLUMNS (
    GROUPBY (
        TABLE2,
        TABLE2[WEEK],
        "Col 1", SUMX (
            CURRENTGROUP (),
            IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "A", "B" }, TABLE2[VAL] )
        ),
        "Col 2", SUMX (
            CURRENTGROUP (),
            IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "C", "D" }, TABLE2[VAL] )
        )
    ),
    "Col 3", [Col 1]
        + [Col 2] / 2,
    "Col 4", - [Col 2] / 2
)

3 Replies

  • MFelix's avatar
    MFelix
    Super User
    9 years ago

    Hi joc,

     

    Just add the Type = good to your filter statement:

     

    SUM2 =
ADDCOLUMNS (
    ADDCOLUMNS (
        ADDCOLUMNS (
            SUMMARIZE (
                'TABLE2',
                [WEEK],
                "Col 1", SUMX (
                    FILTER (
                        'TABLE2',
                        [WEEK] = EARLIER ( 'TABLE2'[WEEK] )
                            && ( 'TABLE2'[NAME] = "A"
                            || 'TABLE2'[NAME] = "B" )
                            && Table2[TYPE] = "good"
                    ),
                    [VAL]
                )
            ),
            "Col 2", SUMX (
                FILTER (
                    'TABLE2',
                    [WEEK] = EARLIER ( 'TABLE2'[WEEK] )
                        && ( 'TABLE2'[NAME] = "C"
                        || 'Table2'[NAME] = "D" )
                        && Table2[TYPE] = "good"
                ),
                [VAL]
            )
        ),
        "Col 3", [Col 1]
            + [Col 2] / 2
    ),
    "Col 4", [Col 1] - [Col 3]
)

     

    Regards,

    MFelix

  • v-chuncz-msft's avatar
    v-chuncz-msft
    Community Support
    9 years ago

    joc,

     

    You could also refer to the following DAX.

    Table =
ADDCOLUMNS (
    GROUPBY (
        TABLE2,
        TABLE2[WEEK],
        "Col 1", SUMX (
            CURRENTGROUP (),
            IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "A", "B" }, TABLE2[VAL] )
        ),
        "Col 2", SUMX (
            CURRENTGROUP (),
            IF ( TABLE2[TYPE] = "good" && TABLE2[NAME] IN { "C", "D" }, TABLE2[VAL] )
        )
    ),
    "Col 3", [Col 1]
        + [Col 2] / 2,
    "Col 4", - [Col 2] / 2
)