Calculated column to have same value in both Rows

[Sudharsanan]

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

[arvindsingh802]

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

[Sudharsanan]

Thank you @arvindsingh802 

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

[arvindsingh802]

@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]

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

Cust	prod	Sales	Qty	Year
1	A	1,193	15	2022
1	A	526	7	2021
2	B	1,165	3	2022
2	B	777	2	2021
3	C	1,028	4	2022
3	C	596	3	2021
4	D	802	5	2022
4	D	1,389	10	2021
5	E	794	6	2022
5	E	922	8	2021
6	F	767	4	2022
6	F	171	1	2021
7	G	717	8	2022
7	G	4,303	48	2021
8	H	492	1	2022
8	H	1,350	3	2021
9	I	452	8	2022
9	I	193	4	2021
10	J	445	5	2022
10	J	229	3	2021
11	K	408	5	2022
11	K	1,183	16	2021
12	L	376	3	2022
12	L	242	2	2021
13	M	355	2	2022
13	M	1,420	8	2021
14	N	295	3	2022
14	N	270	3	2021
15	O	235	1	2022
15	O	571	3	2021
16	P	215	10	2022
16	P	1,022	50	2021
17	Q	163	2	2022
17	Q	1,311	18	2021
18	R	119	5	2022
18	R	590	31	2021
19	S	78	5	2022
19	S	41	3	2021
20	T	63	3	2022
20	T	41	2	2021
21	U	61	1	2022
21	U	102	2	2021
22	V	41	1	2022
22	V	154	5	2021
23	W	29	1	2022
23	W	51	2	2021

[NimaiAhluwalia]

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]

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]

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

[Sudharsanan]

thank you @Anonymous 

This query gives me the sum of qty for 2022. but my requirement is as shown below.

i have produts for 2021 and 2022 but the qty for each prouct should be the sum of 2022's qty. i am able to get the qty for the rows with 2022, but i need the same value to be populated in the rows with 2021 as well.

[Anonymous · Accepted solution]

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

[Sudharsanan]

Thanks @Anonymous  it is working now.

can you help me to build the below logic in measure?

i need to build the below logic to get the result 10.45%

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

Cust	prod	Sales	Qty	Year
1	A	1,193	15	2022
1	A	526	7	2021
2	B	1,165	3	2022
2	B	777	2	2021
3	C	1,028	4	2022
3	C	596	3	2021
4	D	802	5	2022
4	D	1,389	10	2021
5	E	794	6	2022
5	E	922	8	2021
6	F	767	4	2022
6	F	171	1	2021
7	G	717	8	2022
7	G	4,303	48	2021
8	H	492	1	2022
8	H	1,350	3	2021
9	I	452	8	2022
9	I	193	4	2021
10	J	445	5	2022
10	J	229	3	2021
11	K	408	5	2022
11	K	1,183	16	2021
12	L	376	3	2022
12	L	242	2	2021
13	M	355	2	2022
13	M	1,420	8	2021
14	N	295	3	2022
14	N	270	3	2021
15	O	235	1	2022
15	O	571	3	2021
16	P	215	10	2022
16	P	1,022	50	2021
17	Q	163	2	2022
17	Q	1,311	18	2021
18	R	119	5	2022
18	R	590	31	2021
19	S	78	5	2022
19	S	41	3	2021
20	T	63	3	2022
20	T	41	2	2021
21	U	61	1	2022
21	U	102	2	2021
22	V	41	1	2022
22	V	154	5	2021
23	W	29	1	2022
23	W	51	2	2021

[Anonymous]

Hi @Sudharsanan 

The formula you provided seems to have an extra or missing ')', please try to correct it yourself.

Measure = 
VAR _sum_of_2022_sales = CALCULATE(SUM('Table'[Sales]),'Table'[Year]=2022)
VAR _sum_of_2021_sales = CALCULATE(SUM('Table'[Sales]),'Table'[Year]=2021)
VAR _sum_of_2021_qty = CALCULATE(SUM('Table'[Qty]),'Table'[Year] = 2021)
VAR _sum_of_2022_qty = CALCULATE(SUM('Table'[Qty]),'Table'[Year] = 2022)
VAR _result = (_sum_of_2022_sales) / ((_sum_of_2021_sales)/(_sum_of_2021_qty)) * _sum_of_2022_qty)))-1
RETURN
_result

Best Regards,
Gao
Community Support Team