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/1Pw4VzygHbSKlhDTo35rFaMjbWMJmZZ0J/edit?usp=sharing&ouid=108506264278584806223&rtpof=true&sd=true

 

Thank you

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

8 Replies

  • AllanBerces's avatar
    AllanBerces
    Post Prodigy
    1 year ago

    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
  • Anonymous's avatar
    Anonymous
    Not applicable
    1 year ago

    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's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      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'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