Forum Discussion

rajibmahmud's avatar
rajibmahmud
Icon for Helper III rankHelper III
9 years ago
Solved

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...
Need Help
  • v-huizhn-msft's avatar
    v-huizhn-msft
    9 years ago

    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's avatar
    v-huizhn-msft
    9 years ago

    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

rajibmahmud's avatar
rajibmahmud
Icon for Helper III rankHelper III
9 years ago

@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
v-huizhn-msft's avatar
v-huizhn-msft
Icon for Microsoft Employee rankMicrosoft Employee
9 years ago

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