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