Straight table - expression column for only one of the dimension values as a comparator

[zakpullen]

Hi,

I have a some survey data. One column holds organisation levels, including the highest organisation level. For example, 10 departments and the company as a whole. In a straight table I want to show the score per question for each department (or selected department), but also the score for the company as a comparator. When I try to do this, a new row is created for the company scores.

The raw data looks something like this:

I would like a straight table that looks something like this:

Many thanks.

[justISO · Accepted solution]

Hi, to get Company score for question, you need to aggregate it. It can be done something like this:

aggr(nodistinct {<Category={'Company'}>} Avg(Score), [Question Number])

Additionally, you can change 'Category' dimension to 'if(Category<>'Company', Category)' and uncheck 'Include null values'. This way you'll see only departments.

[zakpullen]

Excellent. Thank you so much.