cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
chrisread9907
Frequent Visitor

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

 

Capture.PNG

 

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?

 

 

1 ACCEPTED SOLUTION

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

19 REPLIES 19
prajanto
Frequent Visitor

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

Hi,

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

Total = SUM(Data[Value])

Average = AVERAGE(Data[Value])

Hope this helps.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

yes working

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

Hi,

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/
InsightSoporte
Regular Visitor

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

 

POWERBI.JPG

chrisread9907
Frequent Visitor

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

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




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




View solution in original post

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

Measure formatting is in the Modeling tab.





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Sorry but the Date/Time option is greyed out?

Sean
Community Champion
Community Champion

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

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?





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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

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





Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




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
Community Champion
Community Champion

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
Super User
Super User

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.


@ me in replies or I'll lose your thread!!!
Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
YouTube Channel! Microsoft Hates Greg
Check out my latest book!

Helpful resources

Announcements
UG GA Amplification 768x460.png

Launching new user group features

Learn how to create your own user groups today!

November Power BI Update 768x460.png

Check it Out!

Click here to read more about the November 2021 Updates!

M365 768x460.jpg

Microsoft 365 Collaboration Conference | December 7–9, 2021

Join us, in-person, December 7–9 in Las Vegas, for the largest gathering of the Microsoft community in the world.