How to show average column for 12 months as attached.

Lokesh Piplani shared this question 4 days ago
Awaiting Reply

How to show average column for 12 months as attached.

Comments (1)

photo
1

Hi Lokesh,

  • In the first query you create a cross-tab with the month and other field (my example is region) and your metric.
  • Create an append sub query and map the region fields, but for the month map a calculated field to it. Mine is called Label and just shows the text ‘Average’.
  • Create a calculated field for the metric. Because we are aggregated the data up to the month level I did Sum of the metrics / Count Distinct of the month (names or numbers).
  • If you map the new calculated metric to the current one it should then show an additional column called Average with the average in it.

I have added some screenshots and also attached an export of this report using Ski Team as an example. Let me know if this all makes sense.

Thank you,

Paul