Forum Discussion

KH_Mike's avatar
KH_Mike
Helper III
5 years ago
Solved

Calculate Column - Check Row Record

Hi All,

 

I have a data like below table. Now, I would like to create a new column as Slicer for easy comparsion. Is it possible to create under below condition? If not, any other way to acheive the have the same result?

 

Show "Y" only if "Acutal" and "Forecast" both contain the same "YearMonth" record

 

Actual / ForecastYearMonthNew Column
Actual202101Y
Actual202102Y
Actual202103Y
Actual202104Y
Actual202105Y
Forecast202101Y
Forecast202102Y
Forecast202103Y
Forecast202104Y
Forecast202105Y
Forecast202106N
Forecast202107N
Need Help
  • v-angzheng-msft's avatar
    v-angzheng-msft
    5 years ago

    Hi, KH_Mike 

    Try to create a calculated column like below:

    _Col2 =
VAR _Int =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Global"
        ),
        [Year/Month]
    )
VAR _Glo =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Internal"
        ),
        [Year/Month]
    )
RETURN
    IF (
        'Table'[Actual/ Forecast] = "Foecast",
        "N",
        IF ( 'Table'[Year/Month] IN _Glo && 'Table'[Year/Month] IN _Int, "Y", "N" )
    )

    Result:

     

    Hope this helps

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

8 Replies

  • Jihwan_Kim's avatar
    Jihwan_Kim
    Super User
    5 years ago

    Hi, KH_Mike 

    Please check the below.

    It is for creating a new column.

     

     

    New Column CC =
    VAR currentyearmonth = 'Table'[YearMonth]
    VAR currentdescription = 'Table'[Actual / Forecast]
    VAR conditions =
    COUNTROWS (
    FILTER (
    'Table',
    'Table'[Actual / Forecast] <> currentdescription
    && 'Table'[YearMonth] = currentyearmonth
    )
    )
    RETURN
    IF ( conditions = 1, "Y", "N" )

     

    https://www.dropbox.com/s/45iqt5e2owswkee/khmike.pbix?dl=0 

     

     

    Hi, My name is Jihwan Kim.

     

    If this post helps, then please consider accept it as the solution to help other members find it faster, and give a big thumbs up.

     

    Linkedin: linkedin.com/in/jihwankim1975/

    Twitter: twitter.com/Jihwan_JHKIM

    • KH_Mike's avatar
      KH_Mike
      Helper III
      5 years ago

      Hi Jihwan_Kim ,

       

      I can't perform the same on my dateset. I guess the reason maybe my dateset have duplication on "YearMonth". For the same "YearMonth", I have serval rows of records contain different data (e.g.: Sales Name, Customer Name ...etc.). Do you have any ideas to handle this situation? Thank you.

       

      • Jihwan_Kim's avatar
        Jihwan_Kim
        Super User
        5 years ago

        Hi, KH_Mike 

        Thank you for your feedback.

        Sorry that I quite do not understand your last question.

        Please let me know how your expected result looks like.

         

        Thanks.

  • v-angzheng-msft's avatar
    v-angzheng-msft
    Community Support
    5 years ago

    Hi, KH_Mike 

    Try to create a calculated column like below:

    _Col2 =
VAR _Int =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Global"
        ),
        [Year/Month]
    )
VAR _Glo =
    SUMMARIZE (
        FILTER (
            'Table',
            [Actual/ Forecast] = "Actual"
                && [Internal / Global] = "Internal"
        ),
        [Year/Month]
    )
RETURN
    IF (
        'Table'[Actual/ Forecast] = "Foecast",
        "N",
        IF ( 'Table'[Year/Month] IN _Glo && 'Table'[Year/Month] IN _Int, "Y", "N" )
    )

    Result:

     

    Hope this helps

     

    Best Regards,
    Community Support Team _ Zeon Zheng
    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • KH_Mike's avatar
      KH_Mike
      Helper III
      5 years ago

      Hi v-angzheng-msft 

       

      Sorry for my unclear request. The logic should be like this

       

      If [Actual / Forecast] = "Forecast" then "N"

      If [Actual / Forecast] = "Actual" and both Internal & Global under [Internal / Global] contain same [YearMonth] then "Y" else "N"