topic Subtract one column value to each column values in same row in DAX Commands and Tips

Subtract one column value to each column values in same row

ShyamS — Sat, 18 May 2024 16:49:13 GMT

Hi All,

As per my business requirements I have to subtract current year (actual) column values to all the other column(MAR_FCST,PLAN, ACTUAL (2023)) values as shown in the matrix visuals. I am able to that for current year means current year actual column value I am able to subtract from current year mar_fcst, and plan and Actual. But not able to subtract current year actual column value to previous year actual column value.

I am getting correct results in the GTN% GS~

For year 2024 columns.

Explanation is below:

I want to subtract 2024 ACTUAL column value which is 43.8% to other column that is

(43.8-43.8) for plan and (42.8-43.8) for mar-fcst and (42.7-43.8) for plan and (40.9-43.8) for 2023 actual. But I am not able to do that for 2023.

I am using below dax:

please help to guide me how to achieve this.

Thanks in advance for the time and guidance

Re: Subtract one column value to each column values in same row

ShyamS — Sat, 18 May 2024 17:14:52 GMT

Hi Anonymous ,

Need your guidance on above query.

Thank you in advance for your help and guidance.

Re: Subtract one column value to each column values in same row

Anonymous — Mon, 20 May 2024 01:39:51 GMT

Hi ShyamS

Given that I don't understand the structure of your specific data, I can only give you some suggestions that might be useful:

Maybe you can change your measure into this:

GTN % GS ~ = VAR A = [GTN % GS] VAR CurrentDate = MAX ( CV_GMR_PL_APAC_REPORT[Year] ) VAR B = CALCULATE ( [GTN % GS], CV_GMR_PL_APAC_REPORTING[Year] = YEAR ( CurrenDate ), CV_GMR_PL_APAC_REPORTING[Scenario] = "ACTUAL" ) VAR C = ( ( A - B ) * 10000 ) RETURN C

Hope it helps,

Best Regards

Zhengdong Xu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.