cancel
Showing results for
Did you mean:
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

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
Community Champion
```Sum of Duration = CALCULATE(
SUM(SlaveData[duration]),
FILTER(
SlaveData,
SlaveData[If Status Met] = 1 &&
SlaveData[slaveid] = 1 &&
Slavedata[datStart] = TODAY()
)
)```

Proud to be a Super User!

19 REPLIES 19
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

Super User

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
Frequent Visitor

yes working

Frequent Visitor

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

Super User

Hi,

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

Regards,
Ashish Mathur
http://www.ashishmathur.com
Regular Visitor

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

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

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

Proud to be a Super User!

Frequent Visitor

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

Community Champion

Measure formatting is in the Modeling tab.

Proud to be a Super User!

Frequent Visitor

Sorry but the Date/Time option is greyed out?

Community Champion

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

Community Champion

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?

Proud to be a Super User!

Frequent Visitor

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

Community Champion

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

Proud to be a Super User!

Frequent Visitor

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.

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!

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.

Check out my External Tool for Power BI Desktop! Microsoft Hates Greg's Quick Measures
Check out my latest book!

Impactful Individual

Announcements

#### Launching new user group features

Learn how to create your own user groups today!