Solved: Re: New table with calculated lines

joc · ‎07-07-2017

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

MFelix · ‎07-07-2017

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

Regards

Miguel Félix

Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português

View solution in original post

v-chuncz-msft · ‎07-09-2017

@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
)

Community Support Team _ Sam Zha
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.