cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Nico_Branuelo
Helper II
Helper II

SumIf 2 tables

Hi everyone,

 

I'm trying to replicate a table from excel to powerbi,

table1

 

a11
a23
a33
a45
a510

 

table 2

min >=max <sumif
157
52015

 

I need to calculate the 3rd column of the 2nd table based on the min and max criteria. I am still learning and could not seem to find the right solution.

Thank you!

1 ACCEPTED SOLUTION

 

Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text

 

sumif = 
VAR _last_date = MAX('Table 1'[Month])
RETURN
SUMX(
    FILTER(
        'Table 1',
        'Table 1'[Value] >= 'Table 2'[Min >=]
            && 'Table 1'[Value] < 'Table 2'[Max <]
                && 'Table 1'[Month] = _last_date
    ), 
    'Table 1'[Value]
)

 

 

View solution in original post

5 REPLIES 5
SpartaBI
Community Champion
Community Champion

@Nico_Branuelo this is the calculated column in Table 2:

 

 

sumif = 
SUMX(
    FILTER(
        'Table 1',
        'Table 1'[Value] >= 'Table 2'[Min >=]
            && 'Table 1'[Value] < 'Table 2'[Max <]
    ), 
    'Table 1'[Value]
)

 


These are the names I used for Table 1:

SpartaBI_0-1650911794359.png


And here for Table 2:

 

SpartaBI_1-1650911816793.png

 

Thank you for this! Apologies but I have additional problem that I forgot to include.
table1

CategoryValueMonth 
a11Jan 1 2022
a23Feb 1 2022
a33Mar 1 2022
a45Mar 1 2022
a510April 2022

 

What Dax can I use to just filter the latest date? This would also chnage the result in the table 2.

Thank you for your help!

@Nico_Branuelo 
Depends on what you want to chieve: What do you want the result to be and where? In table 2? What result?

the table 2 should become since it would only sum the data for April 2022

Min >=Max <Sumif
150
52010

 

Your month column should be of date type or something numeric. Just not text, cause than the Max is not the lastes date rather the last alphabetical letter in the beginning of the text

 

sumif = 
VAR _last_date = MAX('Table 1'[Month])
RETURN
SUMX(
    FILTER(
        'Table 1',
        'Table 1'[Value] >= 'Table 2'[Min >=]
            && 'Table 1'[Value] < 'Table 2'[Max <]
                && 'Table 1'[Month] = _last_date
    ), 
    'Table 1'[Value]
)

 

 

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

New Date - Check it Out!

Mark your calendars and join us on Thursday, October 6 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors