Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Sum base from two column

Hi good day,

Can anyone help me to correct my measure. I want to calculate the sum of NPT base from two column trade and specific type.

 

CALCULATE(
SUM('Table'[NPTHrs]), Table[Type] =  "Non-productive"

Thank you

Need Help

4 Replies

  • wini_R's avatar
    wini_R
    Solution Supplier
    1 year ago

    Hey AllanBerces,

    Can you please provide data sample in text format, as well as your current and expected results?

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi wini_R thank you for the reply, Basically i want to segregate it by Area but when i put it on table show this, it just duplicate the data. 

      My Measure: 01

      LastWeek_Yrly_PT =
      VAR CurrentWeek = MAX('TRS'[WeekNo:])
      RETURN
      IF(TRS[Total WKly Hrs] > WEEKNUM(TODAY()),
      CALCULATE(
      SUM('TRS'[NPTHrs]), TRS[Type] =  "Direct",
      FILTER(
      ALL('TRS'),
      'TRS'[WeekNo:] <= CurrentWeek  &&
      'TRS'[Year] = MAX('TRS'[Year])
      )
      ))
       
      Measure: 02
      LastWeek_Yrly_NPT =
      VAR CurrentWeek = MAX('TRS'[WeekNo:])
      RETURN
      IF(TRS[Total WKly Hrs] > WEEKNUM(TODAY()),
      CALCULATE(
      SUM('TRS'[NPTHrs]), TRS[Type] =  "Non-productive",
      FILTER(
      ALL('TRS'),
      'TRS'[WeekNo:] <= CurrentWeek  &&
      'TRS'[Year] = MAX('TRS'[Year])
      )
      ))
      Measure 03:
      LastWK_Overall_Yearly_PT = [LastWeek_Yrly_PT]/(([LastWeek_Yrly_PT]+[LastWeek_Yrly_NPT])/12)
      Measure 04:
      Total WKly Hrs =
      VAR CurrentDate = TODAY()
      VAR CurrentWeekNumber = WEEKNUM(CurrentDate, 2)
      VAR LastWeekNo = CurrentWeekNumber -1
      RETURN
      CALCULATE(
      SUM('TRS'[NPTHrs]),
      FILTER(
      ALL('Dimdate Calendar'),
      'Dimdate Calendar'[Week No.] <= LastWeekNo
      && 'Dimdate Calendar'[Year] = YEAR(CurrentDate)
      && 'Dimdate Calendar'[Week No.] >= 1
      )
       
      Thank you
      • Anonymous's avatar
        Anonymous
        Not applicable
        1 year ago

        Hi AllanBerces 

         

        Thanks for the reply from wini_R .

         

        Due to some security policy, I can't open your link. From your formula, my suggestion is that you can try to remove ALL.

         

        For more details about ALL function,  you can read related document link:

        ALL function (DAX) - DAX | Microsoft Learn

         

        If you need further help, could you please consider showing your PBIX in the following form?

        How to provide sample data in the Power BI Forum - Microsoft Fabric Community

         

        Best Regards,
        Yulia Xu

         

        If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.