Calculate delta between values in column based on value in another column

[Anonymous]

Hi,

I have a table that look like this (Table1):

Tag	VersionID	Value
A	1	1,5
B	1	2,1
C	1	4,5
A	2	1,1
B	2	1,4
C	2	2,3
A	3	3,1

And another table that also contains of the Version column (Table2):

Date	VersionID
1.1.2019	1
1.1.2020	2
1.1.2021	3

Since I want to use two slicers to select the VersionIDs I created two copies of Table2 - they are Table21 and Table22, and of course there is a relationship between the VersionID columns.

What I would like to do is to get for each Tag the delta between the values in the "Value" column, based on the VersionID (the user should always select two versions via two slicers in my report to get the deltas), so having selected VersionID 1 and VersionID 2 I want to display in my report a table like this:

Tag	prop_delta
A	0,4
B	0,7
C	2,2

I thought this would be a rather trivial problem, however I'm just not able to get it to work.

What I have so far is the following DAX for my measure: 

prop_delta =
var selected_verID1 = SUMMARIZE(FILTER(Table21,Table21[VersionID] IN ALLSELECTED(Table21[VersionID])),Table21[VersionID])
var selected_verID2 = SUMMARIZE(FILTER(Table22,Table22[VersionID] IN ALLSELECTED(Table22[VersionID])),Table22[VersionID])
RETURN

This gives me correctly the VersionIDs that were selected. So for the rest of my calculation I was hoping that soemthing like that should do the job:

CALCULATE(SUMX(FILTER(Table1, Table1[VersionID] =selected_verID1), Table1[Value])-SUMX(FILTER(Table1, Table1[VersionID] =selected_verID2), Table1[Value]))

However this doesn't work at all and only creates a "A table of multiple values was supllied where a single value was expected" error.

Thanks for any help!

[PaulOlding]

Assuming your model looks something like this

then a prop_delta measure like this

Gives a result like this

[Anonymous]

Hi Paul,

thanks for your advice - however this still gives me the "A table of multiple values was supllied where a single value was expected" error.

[Anonymous]

Ok, In the meantime I did find a relatively simple solution myself which works as long as I only use one var in it (so either selected_verID1 or selected_verID2 has to be replaced by a static number). This works:

prop_delta =
var selected_verID1 = SUMMARIZE(FILTER(Table21,Table21[VersionID] IN ALLSELECTED(Table21[VersionID])),Table21[VersionID])
var selected_verID2 = SUMMARIZE(FILTER(Table22,Table22[VersionID] IN ALLSELECTED(Table22[VersionID])),Table22[VersionID])
RETURN
CALCULATE(CALCULATE(SUMX(FILTER(Table1, Table1[VersionID] =selected_verID1), Table1[Value]))-CALCULATE(SUMX(FILTER(Table1, Table1[VersionID] = 3), Table1[Value])))

.....but if I replace the "3" in the second FILTER by selected_verID2 I get again the same error. Can anyone tell me what's the issue here?

[Anonymous · Accepted solution]

Ok - I finally fixed it by simply creating a new measure and copying my code into this new measure. Now it works - so I guess the error was just a bug.

[Anonymous]