0

I have three calculated columns in my table. How do I group the data so that I have one row per Department that shows the Head of Department and the values of the three calculated columns.

I made a mock dataset seen here Data Before

And here is what I want the data to look like after Data After

Victoria Grant
  • 35
  • 4
  • Personally, I'd probably handle it in a data function but the purely GUI approach would be to do it via a pivot. Check out File -> Add Data Tables -> From Current Analysis and then use a pivot transformation. – coomie Oct 17 '19 at 15:37
  • Thanks! I am in-database so I would prefer to use a data function but am not sure how to go about it – Victoria Grant Oct 18 '19 at 18:00

1 Answers1

0

You can simply display a cross table, with

Horizontal as (Column Names), which if you type it within Properties as an expression corresponds to <[Axis.Default.Names]>

Vertical as Department and then Head of Department, which corresponds to the expression <[Department] NEST [Head of Department]>

Cell values as the expression: Avg([Calculated1]) as [Calculated1], Avg([Calculated2]) as [Calculated2], Avg([Calculated3]) as [Calculated3]

Gaia Paolini
  • 1,044
  • 1
  • 5
  • 4