topic Re: How to subtract a dax measure to a value of imported column? in DAX Commands and Tips

How to subtract a dax measure to a value of imported column?

milkynight — Mon, 04 Oct 2021 09:30:05 GMT

Hi all,

I have a simple question that I only encounter now: Why can't I subtract a measure to an imported column?

Below example that Actual sales (a dax measure) and Target (imported number) and desired result of Var (Actual Sales - Target)

I normally have Measure - Measure and it's simple, but what shall I do in this situation? Thank you.

Name	Target	Actual sales	Var
A	100%	100%	0%
B	100%	80%	-20%
C	100%	70%	-30%

Re: How to subtract a dax measure to a value of imported column?

ERD — Mon, 04 Oct 2021 09:42:05 GMT

milkynight ,

if I correctly understand your situation, you need to substract measure from the current value:

measure = VAR currentTarget = SELECTEDVALUE ( T[Target] ) RETURN [measureName] - currentTarget

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

Re: How to subtract a dax measure to a value of imported column?

VahidDM — Mon, 04 Oct 2021 09:49:48 GMT

Hi milkynight

Try this:

Var=
Var _AS = [Actual sales]
Var _Tar = max(table(Target))
return
_AS-_Tar

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: How to subtract a dax measure to a value of imported column?

milkynight — Mon, 04 Oct 2021 10:09:05 GMT

VahidDM thanks, I guess your DAX can be shortened to: Var = [Actual Sales] - max(table [Target])?

Both long and short version works, but I'm curious why we use max for Target?

Re: How to subtract a dax measure to a value of imported column?

milkynight — Mon, 04 Oct 2021 10:15:46 GMT

ERD thanks so much, it works nicely.

Just curious, does the first clause mean to convert the imported value to Measure? I tried with VALUES originally and it didn't work

VAR currentTarget = SELECTEDVALUE ( T[Target] )

Re: How to subtract a dax measure to a value of imported column?

VahidDM — Mon, 04 Oct 2021 10:20:03 GMT

Hi milkynight

I'm glad you found the solution.

You can not use a column name in the measure to find the value; you must wrap the column in an aggregation function like MIN, MAX, SUM, AVERAGE, etc.

In your case, because you used the Name column, if you use Min or Max, it will return the value corresponding to that row.

https://www.sqlbi.com/articles/row-context-and-filter-context-in-dax/

https://www.sqlbi.com/articles/calculated-columns-and-measures-in-dax/

If this post helps, please consider accepting it as the solution to help the other members find it more quickly.

Appreciate your Kudos!!

Re: How to subtract a dax measure to a value of imported column?

ERD — Mon, 04 Oct 2021 10:53:34 GMT

milkynight , SELECTEDVALUE function is used in a measure to return a value when the context for ColumnName has been filtered down to one distinct value. I'd say read and use column value in further calculations instead of 'convert'.

You can read about the function here: https://dax.guide/selectedvalue/

Also you could use MAX function instead. In this particular case it will do the same.

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

Re: How to subtract a dax measure to a value of imported column?

KatherineS — Thu, 20 Oct 2022 17:23:43 GMT

Thanks you, this solution is good for me