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

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
  • NimaiAhluwalia's avatar
    NimaiAhluwalia
    Continued Contributor
    3 years ago

    Hello Sudharsanan 

     

    What you can possibly do is to create a single DAX with different variables of logic as mentioned above and then combine them in the return statement as per your logic.

     

    Additional advice on creating a measure would have been much easier.

     

    Regards,

     

    IF MY SOLUTION HELPED YOU PLEASE DO HIT A LIKE AND ACCEPT IT AS A SOLUTION IF IT SOLVES YOUR ERROR!

    • Sudharsanan's avatar
      Sudharsanan
      Helper III
      3 years ago

      Thank you NimaiAhluwalia 

       

      I tried my best but not able to get the number what i want, so thought of having it in the columns and use it for measures.

       

      But for this issue, still i am not able to get a solution. I need the number 5 to be populated in both the rows.

       

       

      • Anonymous's avatar
        Anonymous
        Not applicable
        3 years ago

        Hi Sudharsanan ,

         

        Please try

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

        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