Calculated Column to Measure

[AllanBerces]

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/1Pw4VzygHbSKlhDTo35rFaMjbWMJmZZ0J/edit?usp=sharing&ouid=10850...

Thank you

[Anonymous]

Hi, @AllanBerces 

Due to the privacy policy, it is not possible to directly open the link you share, you can directly copy the form without sensitive data and give the effect you expect to achieve.

Best Regards,
Yang
Community Support Team

[AllanBerces]

Hi @Anonymous 

https://docs.google.com/spreadsheets/d/1Pw4VzygHbSKlhDTo35rFaMjbWMJmZZ0J/edit?usp=sharing&ouid=108506264278584806223&rtpof=true&sd=true

Thank you

[AllanBerces]

Hi @Anonymous thank you for the reply, Basically I wan to change my calculated column into a Measure. my table as below.

DESIRED OUT-PUT

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]

)

SUM_NPTCODE =

SUMX (

    FILTER (

        SUMMARIZECOLUMNS (

            'TRS'[Vessel],

            'TRS'[WeekNo:],

            'TRS'[NPTHrs],

            'TRS'[NPT Code]

        ),

         'TRS'[Vessel] = EARLIER ( 'TRS'[Vessel] )

            && 'TRS'[WeekNo:] = EARLIER ( 'TRS'[WeekNo:] )

            && 'TRS'[NPT Code] = EARLIER ( 'TRS'[NPT Code] )

            && TRS[Type] <> "Direct"

    ),

    'TRS'[NPTHrs]

)

Thank you

[Anonymous]

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]

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 · Accepted solution]

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]

Hi @Anonymous thank you very much working perfetly

[AllanBerces]

Hi,

Below are my dax for my calculated Column

SUM_NPTCODE =

SUMX (

    FILTER (

        SUMMARIZECOLUMNS (

            'TRS'[Vessel],

            'TRS'[WeekNo:],

            'TRS'[NPTHrs],

            'TRS'[NPT Code]

        ),

         'TRS'[Vessel] = EARLIER ( 'TRS'[Vessel] )

            && 'TRS'[WeekNo:] = EARLIER ( 'TRS'[WeekNo:] )

            && 'TRS'[NPT Code] = EARLIER ( 'TRS'[NPT Code] )

            && TRS[Type] <> "Direct"

    ),

    'TRS'[NPTHrs]

)

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