Forum Discussion

Sudharsanan's avatar
Sudharsanan
Helper III
3 years ago
Solved

Calculated column to have same value in both Rows

Hi friends, i have below scenerio where we have Qty for 2021 and 2022 for same product. i need to have the 2022 qty in a new colum in both the rows.   Thanks for your support
Need Help
  • Anonymous's avatar
    Anonymous
    3 years ago

    Hi Sudharsanan ,

     

    Please try

    2022-QTY = 
CALCULATE (
    SUM ( 'Table'[Qty] ),
    FILTER ( ALL ( 'Table'[SALES_Year] ), 'Table'[SALES_Year] = 2022 ),
    ALLEXCEPT ( 'Table', 'Table'[SALES_Mel] )
)

    Best Regards,
    Gao
    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

arvindsingh802's avatar
arvindsingh802
Community Champion
3 years ago
Use this
2022QTY = CALCULATE(SUM('Table'[Qty]), ALL('Table'[SALES_Year]),'Table'[SALES_Year] = 2022)
  • Sudharsanan's avatar
    Sudharsanan
    Helper III
    3 years ago

    Thank you arvindsingh802 

    i wanted the qty to be populated in both the rows. is it possibe?

     

    • arvindsingh802's avatar
      arvindsingh802
      Community Champion
      3 years ago

      Sudharsanan  Please create a measure using this

      2022QTY = CALCULATE(SUM('Table'[Qty]), ALL('Table'[SALES_Year]),'Table'[SALES_Year] = 2022)

      this will work as required

      • Sudharsanan's avatar
        Sudharsanan
        Helper III
        3 years ago

        Thanks Again arvindsingh802 

        i will brief you what i am trying to derive. in the below data set, for 1 product i have sales & qty in 2021 & 2022. like wise for all the prod. below is my logic and i should get the value 10.45%

         

        =(Sum of 2022 sales) / ((sum of 2021 sales)/(sum of 2021 qty)) * sum of 2022qty)))-1

         

        CustprodSalesQtyYear
        1A1,193152022
        1A52672021
        2B1,16532022
        2B77722021
        3C1,02842022
        3C59632021
        4D80252022
        4D1,389102021
        5E79462022
        5E92282021
        6F76742022
        6F17112021
        7G71782022
        7G4,303482021
        8H49212022
        8H1,35032021
        9I45282022
        9I19342021
        10J44552022
        10J22932021
        11K40852022
        11K1,183162021
        12L37632022
        12L24222021
        13M35522022
        13M1,42082021
        14N29532022
        14N27032021
        15O23512022
        15O57132021
        16P215102022
        16P1,022502021
        17Q16322022
        17Q1,311182021
        18R11952022
        18R590312021
        19S7852022
        19S4132021
        20T6332022
        20T4122021
        21U6112022
        21U10222021
        22V4112022
        22V15452021
        23W2912022
        23W5122021