Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Calculated Column to Measure

Hi Good day, Can anyone pls need your help on how can i change my calculated column into a measure (Column Highligthed in Yellow) https://docs.google.com/spreadsheets/d/1Pw4VzygHbSKlhDTo35rFaMj...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi, AllanBerces 

    Please try this, the data you gave does not have the used_AREA field, so there is no way to verify the accuracy of this formula.

    SUM_NPT_by_Vessel/Wk_Measure = 
VAR _vessel = SELECTEDVALUE(TRS[Vessel])
VAR _weekno = SELECTEDVALUE(TRS[WeekNo:])
VAR _used_AREA = SELECTEDVALUE(TRS[used_AREA])
VAR _type = SELECTEDVALUE(TRS[Type])
VAR _re =IF(_type = "Direct", CALCULATE(SUM(TRS[NPTHrs]),FILTER(ALL(TRS),TRS[Vessel] = _vessel && TRS[WeekNo:] = _weekno && TRS[used_AREA] = _used_AREA) ))
RETURN
_re

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi, AllanBerces 

Based on the information you gave, the first formula seems to have the used_AREA field, but the raw data does not. For more information on how the second formula is converted to MEASURE, you can refer to dax below

The second:

 

SUM_NPTCODE_Measure = 
VAR _vessel = SELECTEDVALUE(TRS[Vessel])
VAR _weekno = SELECTEDVALUE(TRS[WeekNo:])
VAR _nptCode = SELECTEDVALUE(TRS[NPT Code])
VAR _type = SELECTEDVALUE(TRS[Type])
VAR _re =IF(_nptCode<>BLANK(), CALCULATE(SUM(TRS[NPTHrs]),FILTER(ALL(TRS),TRS[Vessel] = _vessel && TRS[WeekNo:] = _weekno && TRS[NPT Code] = _nptCode && TRS[Type] = _type) ))
RETURN
_re

 

Best Regards,
Yang
Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago

Hi Anonymous thank you very much for the help how can i change to measure my column

 

SUM_NPT_by_Vessel/Wk =
SUMX (
    FILTER (TRS,
           'TRS'[Vessel] = EARLIER ( 'TRS'[Vessel] )
            && 'TRS'[used_AREA] = EARLIER ( 'TRS'[used_AREA] )
            && 'TRS'[WeekNo:] = EARLIER ( 'TRS'[WeekNo:] )
            && TRS[Type] <> "Direct"
    ),
    'TRS'[NPTHrs]
)
 
Thank you
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    Hi, AllanBerces 

    Please try this, the data you gave does not have the used_AREA field, so there is no way to verify the accuracy of this formula.

    SUM_NPT_by_Vessel/Wk_Measure = 
VAR _vessel = SELECTEDVALUE(TRS[Vessel])
VAR _weekno = SELECTEDVALUE(TRS[WeekNo:])
VAR _used_AREA = SELECTEDVALUE(TRS[used_AREA])
VAR _type = SELECTEDVALUE(TRS[Type])
VAR _re =IF(_type = "Direct", CALCULATE(SUM(TRS[NPTHrs]),FILTER(ALL(TRS),TRS[Vessel] = _vessel && TRS[WeekNo:] = _weekno && TRS[used_AREA] = _used_AREA) ))
RETURN
_re

     

    Best Regards,
    Yang
    Community Support Team

     

    If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly.
    If I misunderstand your needs or you still have problems on it, please feel free to let us know.     Thanks a lot!

    How to get your questions answered quickly --  How to provide sample data in the Power BI Forum

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi Anonymous thank you very much working perfetly