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.

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.