I have the following layout for my report:
where Cost is my dimension measure field calculated for each year at column level for different Products.
Now I need to add the Year field as one of the columns, which is naturally separating the records into different rows like this:
Is there a way that I can have the Year column without splitting my records into separate rows?
If you have a single cost measure then this is just a simple pivot table of product vs year with cost as the measure.
If you have separate cost measures for each year then it gets messy. You can derive a measure using a case statement like
case
when "folder"."year" = 2020 then "folder"."cost(2020)"
when "folder"."year" = 2021 then "folder"."cost(2021)"
...
but this isn't particularly maintainable or performant.
The real solution there would be to implement a single cost measure which leaves you with a simple pivot table