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

[milkynight]

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%

[ERD · Accepted solution]

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

[VahidDM]

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!!

[milkynight]

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

[VahidDM · Accepted solution]

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!!

[ERD · Accepted solution]

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

[KatherineS]

Thanks you, this solution is good for me

[milkynight]

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

[ERD]

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