Forum Discussion

AllanBerces's avatar
AllanBerces
Post Prodigy
1 year ago
Solved

Calculated Column to Measure

Hi can anyone help me to change to measure my calculated column in TRS Table, column (Overall_Cum_PT_YTD) 

 

Overall_Cum_PT_YTD = VAR CurrentDate = 'TRS'[WeekNo:]
VAR Filteredtable = FILTER('TRS','TRS'[WeekNo:]<=CurrentDate )

return

CALCULATE(SUM('TRS'[NPTHrs]), TRS[Type] <> "Non-productive", Filteredtable)

 

https://drive.google.com/file/d/1i1JojzfjyY7Mp4304xyBoNOKvuQJ595F/view?usp=sharing

 

Thank you

Need Help
  • quantumudit's avatar
    quantumudit
    1 year ago

    Hello AllanBerces 

    To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:

     

    Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate =
    SELECTEDVALUE ( TRS[WeekNo:] )
VAR Filteredtable =
    FILTER (
        ALL ( 'TRS' ),
        TRS[WeekNo:] <= CurrentDate
            && TRS[Type] <> "Non-productive"
    )
RETURN
    IF (
        ISINSCOPE ( TRS[used_AREA] ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable,
            VALUES ( TRS[used_AREA] )
        ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable
        )
    )

     

    I am attaching the Power BI with modified formula in the attachment.

     

    Best Regards,
    Udit


    If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

    Proud to be a Super User

7 Replies

  • quantumudit's avatar
    quantumudit
    Super User
    1 year ago

    Hello AllanBerces 
    Please use the following DAX to create the measure with the same logic that you have used for creating the calculated column.

     

    Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate = SELECTEDVALUE(TRS[WeekNo:])
VAR Filteredtable = FILTER(
    ALL('TRS'),
    TRS[WeekNo:] <= CurrentDate 
    && TRS[Type]  <> "Non-productive"
)

RETURN
CALCULATE(
    SUM('TRS'[NPTHrs]), 
    Filteredtable
)

     

    Additionally, there is a BSPTA calculated column in your TRS that is erroneous. Make sure to fix it or delete it, as it might create issues testing other aspects.

     

    I am also attaching the Power BI file for your reference.

     

    Best Regards,
    Udit

    If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.
    Appreciate your Kudo 👍

    🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
    ✨ Visit My Linktree: LinkTree

     

    Proud to be a Super User

    • AllanBerces's avatar
      AllanBerces
      Post Prodigy
      1 year ago

      Hi quantumudit thank yu very much for the reply, but when i put table show same value for used_AREA, possible to segregate by used_AREA

      Thank you

      • quantumudit's avatar
        quantumudit
        Super User
        1 year ago

        Hello AllanBerces 

        To make the formula work when the [used_Area] column is in context, you can use the following DAX instead:

         

        Overall_Cum_PT_YTD_Measure = 
VAR CurrentDate =
    SELECTEDVALUE ( TRS[WeekNo:] )
VAR Filteredtable =
    FILTER (
        ALL ( 'TRS' ),
        TRS[WeekNo:] <= CurrentDate
            && TRS[Type] <> "Non-productive"
    )
RETURN
    IF (
        ISINSCOPE ( TRS[used_AREA] ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable,
            VALUES ( TRS[used_AREA] )
        ),
        CALCULATE (
            SUM ( 'TRS'[NPTHrs] ),
            Filteredtable
        )
    )

         

        I am attaching the Power BI with modified formula in the attachment.

         

        Best Regards,
        Udit


        If this post helps, then please consider Accepting it as the solution to help other members find it more quickly.
        Appreciate your Kudo 👍

        🚀 Let's Connect: LinkedIn || YouTube || Medium || GitHub
        ✨ Visit My Linktree: LinkTree

        Proud to be a Super User