topic Re: Writing a measure to subtract two columns same table with filters in DAX Commands and Tips

Writing a measure to subtract two columns same table with filters

EMSSS22 — Tue, 30 May 2023 14:33:06 GMT

I have 2 columns that i would like to subtract from one another after applied filters.

Eg.

Column B (with version type x and month number x) - Column A (with version type x and month number x)

What I currently have and is giving me wrong answer:

ForecastCMVariance = CALCULATE(SUM(FilteredCommercial[Forecast]) - CALCULATE(SUM(FilteredCommercial[Capital Rental])))

How do I go about writing this dax?

Re: Writing a measure to subtract two columns same table with filters

johnt75 — Tue, 30 May 2023 15:15:48 GMT

Try something like

ForecastCMVariance = CALCULATE ( SUMX ( FilteredCommercial, FilteredCommercial[Forecast] - FilteredCommercial[Capital Rental] ), FilteredCommercial[Version] = "x", FilteredCommercial[month number] = y )

Re: Writing a measure to subtract two columns same table with filters

tamerj1 — Tue, 30 May 2023 15:16:54 GMT

Hi EMSSS22

Are version type and month number from the same table or different tables? What are the relationship between the tables? When you refer to "x" is it the same value used to filter both columns?

Re: Writing a measure to subtract two columns same table with filters

EMSSS22 — Tue, 30 May 2023 15:19:30 GMT

Yes its the same table.

and

Yes the same filter needs to be applied to both columns.

Thanks 🙂

Re: Writing a measure to subtract two columns same table with filters

tamerj1 — Tue, 30 May 2023 19:14:50 GMT

EMSSS22

Apologies for the late reply. Please try

ForecastCMVariance =
SUMX (
FILTER ( Commercial, Commercial[Version] = "x" && Commercial[month number] = y ),
Commercial[Forecast] - Commercial[Capital Rental]
)