Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

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

Hi,

 

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

 

TagVersionIDValue
A11,5
B12,1
C14,5
A21,1
B21,4
C22,3
A33,1

 

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

 

DateVersionID
1.1.20191
1.1.20202
1.1.20213

 

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:

 

Tagprop_delta
A0,4
B0,7
C2,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!

 

  • Anonymous's avatar
    Anonymous
    5 years ago

    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.

5 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable

    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's avatar
      Anonymous
      Not applicable

      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.

  • Assuming your model looks something like this

     

    then a prop_delta measure like this

     

     

    Gives a result like this

     

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable

      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.