Forum Discussion

BachFel's avatar
BachFel
Helper II
8 years ago
Solved

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]. T...
Need Help
  • Zubair_Muhammad's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    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's avatar
    Zubair_Muhammad
    8 years ago

    Hi BachFel

     

    Try this

     

    WorkingTime_Jan =
CALCULATE (
    SUM ( LN[ArbeitszeitInH] ),
    FILTER (
        VALUES ( LN[Dates] ),
        MONTH ( LN[Dates] ) = 1
            && YEAR ( LN[Dates] ) = 2018
    )
)
Zubair_Muhammad's avatar
Zubair_Muhammad
Community Champion
8 years ago

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's avatar
BachFel
Helper II
8 years ago

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's avatar
    Zubair_Muhammad
    Community Champion
    8 years ago

    Hi BachFel

     

    Looks good to me. May be I am missing something

     

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

    • BachFel's avatar
      BachFel
      Helper II
      8 years ago

      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's avatar
        Zubair_Muhammad
        Community Champion
        8 years ago

        Hi BachFel

         

        Try this

         

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