I have a dataset that has four columns: Country, Variable, Value, Year, and Vintage. The Country denotes country names of different 69 countries, Variables are 40 economic, Value is the actual numerical value for each variable, the year ranges from 2019-2028 and the Vintage is a text that denotes a date in which a set of values was published (say “June 2023”, we two Vintages per year). So a short extract of the dataset would looks like the example below:
Country | Variables | Value | Year | Vintage |
Country_1 | GDP growth GDP growth GDP growth GDP growth | [number 1] [number 2] [number 3] [number 4] | 2022 2023 2022 2023 | Oct 2020 Oct 2020 Oct 2021 Oct 2021 |
Country_1 | Inflation Inflation Inflation Inflation | [number 1] [number 2] [number 3] [number 4] | 2022 2023 2022 2023 | Oct 2020 Oct 2020 Oct 2021 Oct 2021 |
Country_2 | GDP growth GDP growth GDP growth GDP growth | [number 1] [number 2] [number 3] [number 4] | 2022 2023 2022 2023 | Oct 2020 Oct 2020 Oct 2021 Oct 2021 |
Country_2 | Inflation Inflation Inflation Inflation | [number 1] [number 2] [number 3] [number 4] | 2022 2023 2022 2023 | Oct 2020 Oct 2020 Oct 2021 Oct 2021 |
Country_ 3 . . . | … | … | … | … |
My objective is to set a page in which the user can select: 1) a Variable, and 2) two Vintages for which we need to compute the difference in the Values. For example, in the table above if the user selected the Variable: “GDP growth”, Vintage 1 = October 2020, and Vintage 2 = Oct 2021, the resulting column should list, for all the countries in the data, a column with the difference in the values of these two vintages for each year. For example:
Country | Year | Change in Value for “GDP Growth” between Vintages |
Country_1 Country_1 | 2022 2023 | Diff= [Number 3 – Number 1] Diff =[Number 4 – Number 2] |
Country _2 Country _2 | 2023 | Diff= [Number 3 – Number 1] Diff [Number 4 – Number 2] |
I believe this would be straightforward if we could compute calculate columns, but we have too many vintages and variables to create one for all the individual options. Hence wee need the column to respond dynamically to the selection of Variable and Vintage, but measures seem to result in one unique value not a column.
I can link a slicer to Variable, and other one to Vintage (renames ad Vintage1). In order to allow the user to select a second Vintage option, I made a copy of the Column Vintage1 and relabeled it as Vintage2. Then I inserted two Table visuals in a page, I used the “edit interactions” option so that one of the Table visuals responds to changes in the slicers for Variables and Vintage1 (but not Vintage2) and the other Table visual to respond to slicers Variable as Vintage2 (but not Vinatge1). So I have already “two Table visuals” with the rows as countries, and the values for the selected variable in the two different Vintages selected. The problem is that I have not found a way to bring the values in these two separate visual tables into one Table, so that I can compute the difference between thw two vintages for each country/year/variable. Yet these two tables seem to be totally unrelated and I cant fid a way to extract the values I need from them to make the computation.
