topic Re: Calculate delta between values in column based on value in another column in DAX Commands and Tips

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

Anonymous — Fri, 07 May 2021 09:54:00 GMT

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:

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!

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

Anonymous — Fri, 07 May 2021 11:50:46 GMT

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

Anonymous — Fri, 07 May 2021 12:50:42 GMT

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?

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

PaulOlding — Sun, 09 May 2021 17:06:23 GMT

Assuming your model looks something like this

then a prop_delta measure like this

Gives a result like this

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

Anonymous — Mon, 10 May 2021 13:28:53 GMT

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.

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

Anonymous — Mon, 10 May 2021 13:41:38 GMT

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.