cancel
Showing results for
Search instead for
Did you mean:
BachFel Regular Visitor

## SUMIFS Problem

Ho together,

I´m new at PBI and look for an aquivalent for the Sum IF Function in Excel:

I have to tables. 1. one is calles "KontElement" and contains the columns [CSAuftrag] and [Budget].

The 2. one contains several operations per each CS-Auftrag Number.

CSAuftrag   Budget

1                  40

2                  50

Table 2:

CsAuftrag    Leistung

1                   5

1                   7

2                   9

1                   8

2                   9

2                   4

What I want to have:

CSAuftrag   Budget    Leistung (Sum!)

1                   40           20

2                   50           22

Anybody who can help me, I´m kind of lost right now... =(

Greetings,

Fel

3 ACCEPTED SOLUTIONS

Accepted Solutions Super User

## Re: SUMIFS Problem

Hi @BachFel

You can use this calculated column in Table 1 i.e. KontElement

Assuming 2 tables are related on column CSAuftrag

```=
CALCULATE ( SUM ( Table2[Leistung] ) )
``` Super User

## Re: SUMIFS Problem

Hi @BachFel

Looks good to me. May be I am missing something

Could you show what you are getting and what you expect?

Highlighted Super User

## Re: SUMIFS Problem

Hi @BachFel

Try this

```WorkingTime_Jan =
CALCULATE (
SUM ( LN[ArbeitszeitInH] ),
FILTER (
VALUES ( LN[Dates] ),
MONTH ( LN[Dates] ) = 1
&& YEAR ( LN[Dates] ) = 2018
)
)
```
5 REPLIES 5 Super User

## Re: SUMIFS Problem

Hi @BachFel

You can use this calculated column in Table 1 i.e. KontElement

Assuming 2 tables are related on column CSAuftrag

```=
CALCULATE ( SUM ( Table2[Leistung] ) )
```

BachFel Regular Visitor

## Re: SUMIFS Problem

the two table are related. They are related on column KontElementID, which is also good?

But the calculated numbers are wrong.. I created following calculated column:

`IstLeistung = Calculate ( Sum (LN[ArbeitszeitInH])` Super User

## Re: SUMIFS Problem

Hi @BachFel

Looks good to me. May be I am missing something

Could you show what you are getting and what you expect?

BachFel Regular Visitor

## Re: SUMIFS Problem

there is another task:

`WorkingTime_Jan = Calculate ( Sum (LN[ArbeitszeitInH]))`

There is a column with dates in the table "LN". How can I improve the formula above to get only the operations of january 2018?

The format of the dates is: dd.mm.yyyy

Highlighted Super User

## Re: SUMIFS Problem

Hi @BachFel

Try this

```WorkingTime_Jan =
CALCULATE (
SUM ( LN[ArbeitszeitInH] ),
FILTER (
VALUES ( LN[Dates] ),
MONTH ( LN[Dates] ) = 1
&& YEAR ( LN[Dates] ) = 2018
)
)
```