SUMIF Function

[chrisread9907]

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?

[Anonymous]

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]

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]

yes working

[Anonymous]

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

[Ashish_Mathur]

Hi,

Try it first and let me know if it does not meet your expectation.

[InsightSoporte]

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

[chrisread9907]

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

[KHorseman · Accepted solution]

Sum of Duration = CALCULATE(
	SUM(SlaveData[duration]),
	FILTER(
		SlaveData,
		SlaveData[If Status Met] = 1 &&
		SlaveData[slaveid] = 1 &&
		Slavedata[datStart] = TODAY()
	)
)

[chrisread9907]

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

[KHorseman]

Measure formatting is in the Modeling tab.

[chrisread9907]

Sorry but the Date/Time option is greyed out?

[Sean]

You probably need to add the duration to a date...

[KHorseman]

Then the value your measure is returning isn't a valid date. To be honest I'm not surprised; I don't see how a sum of durations would ever equal a calendar date. It doesn't make sense. What date is 5 hours 27 minutes + 3 days 8 hours 12 minutes, for instance?

[chrisread9907]

Yes that is true, i was looking for a 24 hour clock displaying the total duaration.

My total durations will never go over 23:59 

Even if both are formatted as number i get 'cant display the visual' error

[KHorseman]

What kind of visual are you trying to use this in?

[chrisread9907]

So, I have a 2 duation columns one formated as a number in seconds, one formated as time in hours, 

I would like it to appear in a visual table, i can then also refrence it in another measure.

[Sean]

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]

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.

[itchyeyeballs]

have a look at this

http://www.powerpivotpro.com/2009/12/powerpivot-dax-calculate-is-a-supercharged-sumif/

and 

http://www.powerpivotpro.com/2014/10/sum-sumx-or-calculatechoices-choices/

will be a good starting point