cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
BachFel Regular Visitor
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
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
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
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
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
Regular Visitor

Re: SUMIFS Problem

Hi @Zubair_Muhammad

 

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

But the calculated numbers are wrong..

 

2.JPG

 

I created following calculated column:

 

IstLeistung = Calculate ( Sum (LN[ArbeitszeitInH])
Super User
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
Regular Visitor

Re: SUMIFS Problem

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

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