DAX equivalent of =sumifs, based on row context

[Daniboy50]

"Table 1" contains my data.
"Table 2" contains various combinations of filters, that I would like to use on my data, and then display "Table 2" on a matrix visual.
Picture below.

In excel, I can achieve this by a simple "=sumifs()" formula. How do I replicate this in DAX?

The closest comparable thread I found was this, but it doesn't offer a solution.

=filter() function does not work, because it requires you to specify a static filter, for example 'Mode of Transport' = "Ship". Instead, it should be dynamic, i.e. 'Table 1'[Mode of Transport] = 'Table 2' [Column G]

Using a simple =sum() formula and later applying filters on a matrix visual is also not an option, because filters have to be unique for each row, not for the entire visual. The above is dummy data; real data in Table 2 has thousands of filter combinations, hence creating a Matrix visual for each unique pair is not an option.

Appreciate the help!

[v-rzhou-msft · Accepted solution]

Hi @Daniboy50 ,

According to your statement, I think you just need to create a calculated column in 'Table 2'.

Value Sum = 
CALCULATE (
    SUM ( 'Table 1'[Shipment Value] ),
    FILTER (
        'Table 1',
        'Table 1'[Mode Of Transport] = EARLIER ( 'Table 2'[Mode of Transport] )
            && 'Table 1'[Fragile] = EARLIER ( 'Table 2'[Fragile] )
            && 'Table 1'[Priority] = EARLIER ( 'Table 2'[Priority] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Daniboy50 · Accepted solution]

Thank you everyone for the help! I finally found the solution, using 'SelectedValue' formula. 

Step 1: Create a new column in Table1, using 'SelectedValue' formula. This column, will return value from Table 2, column F, based on values in Table1, columns B,C & D.
Step2: Put this new column on a matrix visual.

This effectively allows you to filter data in Table1, by using very complex filters that are defined in Table 2.
Sorry if the original questions wasn't super clear!

Best,

[Jihwan_Kim]

Hi,

I am not sure if I understood your question correctly, but if your desired output is creating a table, please check the below picture and the attached pbix file.

Result table = 
ADDCOLUMNS (
    CROSSJOIN (
        ALL ( Data[Mode of Transport] ),
        ALL ( Data[Fragile] ),
        ALL ( Data[Priority] )
    ),
    "@ValueSum",
        SUMX (
            FILTER (
                Data,
                Data[Mode of Transport] = EARLIER ( Data[Mode of Transport] )
                    && Data[Fragile] = EARLIER ( Data[Fragile] )
                    && Data[Priority] = EARLIER ( Data[Priority] )
            ),
            Data[Shipment Value]
        )
)

[Daniboy50]

Thank you so much! This is almost what I was looking for.

My goal isn't exactly to create the "Table 2". I already have a table which contains the information of specific combinations of filters I need. That information is stored in columns G,H and I. The only thing missing from that table, is the equivalent of "=sumifs()" formula in column J.

What you have created is a table with every possible combination of those filters, which is more than I wish to have. I would like to see the sums of only 3 specific combinations of filters shown in my example:

[v-rzhou-msft · Accepted solution]

Hi @Daniboy50 ,

According to your statement, I think you just need to create a calculated column in 'Table 2'.

Value Sum = 
CALCULATE (
    SUM ( 'Table 1'[Shipment Value] ),
    FILTER (
        'Table 1',
        'Table 1'[Mode Of Transport] = EARLIER ( 'Table 2'[Mode of Transport] )
            && 'Table 1'[Fragile] = EARLIER ( 'Table 2'[Fragile] )
            && 'Table 1'[Priority] = EARLIER ( 'Table 2'[Priority] )
    )
)

Result is as below.

Best Regards,
Rico Zhou

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Jihwan_Kim]

Hi,

If you want to create a table that only shows three specific rows, please check the attached file and the below DAX formula for creating a table.

Result table =
FILTER (
    ADDCOLUMNS (
        CROSSJOIN (
            ALL ( Data[Mode of Transport] ),
            ALL ( Data[Fragile] ),
            ALL ( Data[Priority] )
        ),
        "@ValueSum",
            SUMX (
                FILTER (
                    Data,
                    Data[Mode of Transport] = EARLIER ( Data[Mode of Transport] )
                        && Data[Fragile] = EARLIER ( Data[Fragile] )
                        && Data[Priority] = EARLIER ( Data[Priority] )
                ),
                Data[Shipment Value]
            )
    ),
    ( Data[Mode of Transport] = "Truck"
        && Data[Fragile] = "No"
        && Data[Priority] = "No" )
        || ( Data[Mode of Transport] = "Plane"
        && Data[Fragile] = "Yes"
        && Data[Priority] = "No" )
        || ( Data[Mode of Transport] = "Train"
        && Data[Fragile] = "Yes"
        && Data[Priority] = "Yes" )
)

If you want to show the SUM in card visualization, then create a below measure and then put it into a card visualization.

Condition sum measure: =
CALCULATE (
    SUM ( Data[Shipment Value] ),
    FILTER (
        Data,
        ( Data[Mode of Transport] = "Truck"
            && Data[Fragile] = "No"
            && Data[Priority] = "No" )
            || ( Data[Mode of Transport] = "Plane"
            && Data[Fragile] = "Yes"
            && Data[Priority] = "No" )
            || ( Data[Mode of Transport] = "Train"
            && Data[Fragile] = "Yes"
            && Data[Priority] = "Yes" )
    )
)