SUMIFS Problem

[BachFel]

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

[Zubair_Muhammad · Accepted solution]

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] ) )

[Zubair_Muhammad · Accepted solution]

Hi @BachFel

Looks good to me. May be I am missing something

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

[Zubair_Muhammad · Accepted solution]

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]

Hi @Zubair_Muhammad

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])

[Zubair_Muhammad · Accepted solution]

Hi @BachFel

Looks good to me. May be I am missing something

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

[BachFel]

Hi @Zubair_Muhammad

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

[Zubair_Muhammad · Accepted solution]

Hi @BachFel

Try this

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