Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
joc
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
MFelix
Super User
Super User

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

v-chuncz-msft
Community Support
Community Support

@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
joc
Helper I
Helper I

Thanks a lot for your responses ! 🙂

v-chuncz-msft
Community Support
Community Support

@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.
MFelix
Super User
Super User

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



Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.