Forum Discussion

chrisread9907's avatar
chrisread9907
Frequent Visitor
9 years ago
Solved

SUMIF Function

Hi,

 

I am struggling to covert my SUMIF Excel formula to DAX,

 

This is my excel formula:

 

=SUMIFS('Slave Data'!$E:$E,'Slave Data'!K:K,"=1",'Slave Data'!$B:$B,"="&$B10,'Slave Data'!L:L,"="&H1)

 

This is the sample data from excel

 

 

Within BI i have the same columns and would like to create a measure to equal the output of the excel formula,

 

How can i create a formua with multiple conditions?

 

 

Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago
    Sum of Duration = CALCULATE(
	SUM(SlaveData[duration]),
	FILTER(
		SlaveData,
		SlaveData[If Status Met] = 1 &&
		SlaveData[slaveid] = 1 &&
		Slavedata[datStart] = TODAY()
	)
)

19 Replies

  • Sean's avatar
    Sean
    Community Champion
    9 years ago

    It seems you want the user to enter the values in individual cells - H1 and B10?

    You can't do this in PBI.

     

    But you can get all results for your data set basically by using this formula

     

     

    Measure =
CALCULATE (
    SUM ( Table[E] ),
    Table[K] = 1,
    ALLEXCEPT ( Table, Table[B], Table[L] )
)

    This will give you the sum in column E for all combinations of columns B & L where K is 1

     

    Then create a Table visualization

    Add Columns B and L (make sure you select Do Not Summarize for Both) and then add the Measure

     

    Hope this helps!

     

     

     

  • Greg_Deckler's avatar
    Greg_Deckler
    Community Champion
    9 years ago

    I'm not super familiar with Excel's SUMIFS statement so you'd have to explain what your formula is doing. But, you *should* be able to replace it with a DAX equivalent, even if it just a bunch of IF statements.

  • chrisread9907's avatar
    chrisread9907
    Frequent Visitor
    9 years ago

    Thanks, So the written formula would be

     

    SUM of SlaveData[duration] if Slavedata[If Status Met] =1 and if Slavedata[slaveid] =1 and if Slavedata[datStart] = today

     

    Sorry for not explianing properly,

     

    Chris

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago
      Sum of Duration = CALCULATE(
	SUM(SlaveData[duration]),
	FILTER(
		SlaveData,
		SlaveData[If Status Met] = 1 &&
		SlaveData[slaveid] = 1 &&
		Slavedata[datStart] = TODAY()
	)
)
      • chrisread9907's avatar
        chrisread9907
        Frequent Visitor
        9 years ago

        Thank you very much, how can i format this as a date to display it in a visual?

  • InsightSoporte's avatar
    InsightSoporte
    Regular Visitor
    7 years ago

    I have moths looking for a solution about this mesure or calculate.

     

    This table has a group of variable  in multiple columns in excel file , i need to find each variable in all Q1_# and count, also and get % for the sum of all variable.

     

    In the image you can find the chart to expect and the output table.

     

    Using "=COUNTIF(EXCELTABLE,VARIABLETOFIND)/COUNTA(Q1_#)" 
    *Q1_# All columns Q1

     

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    hi

    I have two simple questions, how do I calculate what is described below.
    Year value
    2019 200
    2018 100
    2018 100
    2019 200
    2019 100
    2018 50
        
    The average year = 2019 is 166.67
    Add up if Year = 2019 is 500

    • Ashish_Mathur's avatar
      Ashish_Mathur
      Super User
      6 years ago

      Hi,

      Drag Year to the row labels of your visual.  Write these measures

      Total = SUM(Data[Value])

      Average = AVERAGE(Data[Value])

      Hope this helps.

      • Anonymous's avatar
        Anonymous
        Not applicable
        6 years ago

        Thank you for the answer, with a formula like that do all of them count? What if only 2019 counted.