Adding Multiple Headings to Cross Table, depending on Column Name

[Matt Semmens]

Good afternoon,

I am currently using a cross table to compile a number of calculations.

In an existing excel solution, these different calculations are grouped under 3 seperate headings (see subheadings .png below)

Within my cross table these 8 columns are all calculated within the "Cell Values" section, and using "(Column Names)" on the Horizontal Axis, allows me to see those 8 columns individually, however, I was looking to categorize them as per the excel screenshot attached, I can't seem to find a way to reference the individual names of the measures, essentially I would want to use a case statement to dictate which Category the different KPIs fall under but can't seem to find a way to code it...

 

This returns all results as "Clawback", so the case statement is not invidiaully evaluating the column names.

Does anybody know of a way to get the result that I'm after

Kind Regards,

 

 

Matt

[Fabian Duerr]

I can't help you with your script, but I think you could add a calculated column with your case statement to the original data table. Then, also use this column in your cross table. This shouldn't require any scripting. Would this work

[Kevin Flynn]

Curious.

Can you just put in a calulated field like below:

 

Case When DisplayName in ("UK","EU","Import") then 'UK'

when DisplayName in ("1yr","2yr","5yr") then 'Forecast'

else 'Clawback' end as [Header]

Then create a cross table, and add the [Header] Field, then the DisplayName field.

[Reed Woodworth]

Trying to find a solution for this as well but no luck. I'm trying below as an expression on my horizontal axis, but it's returning the "Else" value (YTD in my case) for all of the columns incorrectly. Appreciate anyone that can help out!

<CASE WHEN DocumentProperty("Axis.Measures.DisplayName") IN ("TTM Sales", "TTM Cost", "TTM GPM") THEN "TTM"

ELSE "YTD" END as [Header]

NEST [Axis.Default.Names]>