cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
rajibmahmud Helper III
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.

 

YearPeriodVersionProductCY Sales
2016JanActualIce Cream110
2016FebActualIce Cream101
2016MarActualIce Cream167
2016AprActualIce Cream141
2016MayActualIce Cream146
2016JanActualCake126
2016FebActualCake139
2016MarActualCake103
2016AprActualCake117
2016MayActualCake124
2017JanActualIce Cream136
2017FebActualIce Cream113
2017MarActualIce Cream101
2017AprActualIce Cream130
2017MayActualIce Cream173
2017JanActualCake164
2017FebActualCake181
2017MarActualCake192
2017AprActualCake187
2017MayActualCake150

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Microsoft v-huizhn-msft
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)


1.PNG

Best Regards,
Angelia

View solution in original post

Microsoft v-huizhn-msft
Microsoft

Re: SUMIFS in BI

Hi @rajibmahmud,

Please create a new table by clicking "New Table" under Modeling on home page using the formula. Please learn more about SUMMARIZE from here.

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

View solution in original post

3 REPLIES 3
Microsoft v-huizhn-msft
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)


1.PNG

Best Regards,
Angelia

View solution in original post

rajibmahmud Helper III
Helper III

Re: SUMIFS in BI

@v-huizhn-msft

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:

YearPeriodVersionProductCY Sales
2016JanActualIce Cream110
2016JanActualIce Cream101
2016JanActualIce Cream167
2016AprActualIce Cream141
2016MayActualIce Cream146
Microsoft v-huizhn-msft
Microsoft

Re: SUMIFS in BI

Hi @rajibmahmud,

Please create a new table by clicking "New Table" under Modeling on home page using the formula. Please learn more about SUMMARIZE from here.

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

View solution in original post

Helpful resources

Announcements
New Ranks Launched March 24th!

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!

‘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

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 Microsoft Power Platform event series.

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

Community Summit North America

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

Top Solution Authors