cancel
Showing results for
Did you mean:
Helper III

## SUMIFS in BI

I am trying to do a sumifs in BI table. In excel, it can done using

SUMIFS(E:E,D: D,Table1[@Product],B:B,Table1[@Period],A:A,Table1[@Year]-1).

What I am trying to do is to calculate PY Sales value from below table where I have both 2016 & 2017. My target is to get PY Value in a calculated column for 2017 and 2016, it can be expressed in zero.

 Year Period Version Product CY Sales 2016 Jan Actual Ice Cream 110 2016 Feb Actual Ice Cream 101 2016 Mar Actual Ice Cream 167 2016 Apr Actual Ice Cream 141 2016 May Actual Ice Cream 146 2016 Jan Actual Cake 126 2016 Feb Actual Cake 139 2016 Mar Actual Cake 103 2016 Apr Actual Cake 117 2016 May Actual Cake 124 2017 Jan Actual Ice Cream 136 2017 Feb Actual Ice Cream 113 2017 Mar Actual Ice Cream 101 2017 Apr Actual Ice Cream 130 2017 May Actual Ice Cream 173 2017 Jan Actual Cake 164 2017 Feb Actual Cake 181 2017 Mar Actual Cake 192 2017 Apr Actual Cake 187 2017 May Actual Cake 150

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft

## Re: SUMIFS in BI

Hi @rajibmahmud

Please use LOOKUP function, you can use mutiple conditions <search_columnName>, <search_value> like the criteria_range1, criteria1 in SUMIFS function.

Create a calculated column using the formula. And see the expected result shown in the screenshot below.

`Column = LOOKUPVALUE(Table1[CY Sales],Table1[Product],Table1[Product],Table1[Period],Table1[Period],Table1[Year],Table1[Year]-1)`

Best Regards,
Angelia

Microsoft

## Re: SUMIFS in BI

Hi @rajibmahmud,

`NewTable=summarize(Table,Table[Period],Table[Version],Table[Product],"CY Sales",SUM(Table[CYSales]))`

Then you will get single product in single month. Then create the calclated column using the formula above based on the new table.

Best Regards,
Angelia

3 REPLIES 3
Microsoft

## Re: SUMIFS in BI

Hi @rajibmahmud

Please use LOOKUP function, you can use mutiple conditions <search_columnName>, <search_value> like the criteria_range1, criteria1 in SUMIFS function.

Create a calculated column using the formula. And see the expected result shown in the screenshot below.

`Column = LOOKUPVALUE(Table1[CY Sales],Table1[Product],Table1[Product],Table1[Period],Table1[Period],Table1[Year],Table1[Year]-1)`

Best Regards,
Angelia

Helper III

## Re: SUMIFS in BI

Thanks a lot.

Will it do sumifs or lookup? In case of multiple value for single Product in a single month, will it work?

Example:

 Year Period Version Product CY Sales 2016 Jan Actual Ice Cream 110 2016 Jan Actual Ice Cream 101 2016 Jan Actual Ice Cream 167 2016 Apr Actual Ice Cream 141 2016 May Actual Ice Cream 146
Microsoft

## Re: SUMIFS in BI

Hi @rajibmahmud,

`NewTable=summarize(Table,Table[Period],Table[Version],Table[Product],"CY Sales",SUM(Table[CYSales]))`

Then you will get single product in single month. Then create the calclated column using the formula above based on the new table.

Best Regards,
Angelia

Announcements

#### New Ranks Launched March 24th!

The time has come: We are finally able to share more details on the brand-new ranks coming to the Power BI Community!

#### ‘Better Together’ Contest Finalists Announced!

Congrats to the finalists of our ‘Better Together’-themed T-shirt design contest! Click for the top entries.

#### Arun 'Triple A' Event Video, Q&A, and Slides

Missed the Arun 'Triple A' event or want to revisit it? We've got you covered! Check out the video, Q&A, and slides now.

#### Join THE global Power Platform event series.

Attend for two days of expert-led learning and innovation on topics like AI and Analytics, powered by Dynamic Communities.

#### Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Top Solution Authors
Top Kudoed Authors