How to get field into table column?

[jonescm128]

Hello,

I am trying to build a table in which one of my fields can be used as a column. The dimension is CY and I would like the columns to be 2017, 2018, 2019 and 2020 which are values in my data. I then want use SUM(RECORD_ID) as my measure. 

So my columns would be the year and my row would be the SUM(RECORD_ID) for that particular year. 

Can this be done?

[Kushal_Chawda]

@jonescm128  Not sure I understood it completely.  How about creating pivot table with Year as Column and measure?

[jonescm128]

Im not sure a pivot table would work. Ultimately, below is a screen shot of what I would like except the first column (CY) would be divided into four columns for 2017, 2018, 2019 and 2020 and my measure would be the sum of discharges (far right column). This way, it shows a quick trend from year to year.

[jonescm128]

So I think I figured it out. It was much simpler than I anticipated. I just added a CY expression to my SUM measure field for year and did that four times, one for column I wanted represented.

[Kushal_Chawda · Accepted solution]

@jonescm128  I think pivot table will be the good option here which you can always set it as fully expanded so that it will look like table. Because you can make it your pivot table dynamic if new year is added.

If you use table then you may need to create the 4 measures to display values for each year . In this case you need to create measure manually if new year is added.

You could do something like below in table.

Remove CY from dimension and create 4 measures for each year

=sum({<CY={2017}>}RECORD_ID) -- Measure for 2017

=sum({<CY={2018}>}RECORD_ID) -- Measure for 2018

=sum({<CY={2019}>}RECORD_ID) -- Measure for 2019

and so on