SUMIFS in BI

[rajibmahmud]

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

[v-huizhn-msft · Accepted solution]

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

[v-huizhn-msft · Accepted solution]

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

[rajibmahmud]

@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:

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

[v-huizhn-msft · Accepted solution]

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