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-pro...
Need Help
  • Anonymous's avatar
    Anonymous
    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.

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.

      • AllanBerces's avatar
        AllanBerces
        Post Prodigy
        1 year ago

        Hi Anonymous thank you very much for the reply working perfectly.