cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

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

 

table1.png

 

 

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

 

table2.png

 

 

 

Thanks a lot for your help,

Joc

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Super User III
Super User III

Re: New table with calculated lines

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

filter.png

 

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

Highlighted
Community Support
Community Support

Re: New table with calculated lines

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

View solution in original post

3 REPLIES 3
Highlighted
Super User III
Super User III

Re: New table with calculated lines

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

filter.png

 

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

Highlighted
Community Support
Community Support

Re: New table with calculated lines

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

View solution in original post

Highlighted
Helper I
Helper I

Re: New table with calculated lines

Thanks a lot for your responses ! 🙂

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors