Refer to dynamic column names and use them for calculations

[Anonymous]

Hi all,

I have recently started using Power BI, so please excuse me if this is a stupid question...

I have a data set which consists of a column "Time/date" with 15-minute intervalls (01.01.2020 00:00:00, 01.01.2020 00:00:15, ...) and columns represanting different time series. So:

Time/Date                 Time Series 1        Time Series 2         Time Series 3        ...

01.01.2020 00:00:00         $500                      $250                         $40                ...

01.01.2020 00:00:15         $300                      $260                         $10                ....

In total there are more than 500 time series, which lead to more than 15 million values. 

There are different manufacturers and for example "Time Series 1" and "Time Series 3" belong to "Manufacturer 1" and calculate the indicator "Profit", while "Time series 2" and "Time series 4" belong to "Manufacturer 2" and calculate the indicator "Revenue". In total, there are more than 40 manufacturers and more than 50 indicators.

I have created an overview table as follow:

Manufacturer           Indicator                 Name of time series              Data source

Manufacturer 1           Profit                        Time Series 1                            XYZ

Manufacturer 1           Profit                        Time Series 3                            XYZ

Manufacturer 2          Revenue                    Time Series 2                            YYY

...

Is there a way to say: For the indicator = "Profit" and Manufacturer = "Manufacturer 1" go through the overview table and extract the names of the time series ("Time Series 1" and "Time Series 3") and the Data source ("XYZ"). Based on this data, go to the Table of the data source ("XYZ") and search for the column name "Time Series 1" and "Time Series 3". Then add the values of the two columns and return the value.

Thank you very much in advance!

Best regards,

Tobias

[MFelix · Accepted solution]

Hi @Anonymous ,

Believe the best option is to go to the data set and unpivot your columns, having such a large number of colums select all the columns that aren't going to be unpivot and select unpivot others.

Then make a relationship between the new column create and the overview.