Forum Discussion

amelia's avatar
amelia
ETL
09-12-2022

Grouping Column Headers

For the pivot table widget, would it be possible to group columns together and add category headers for the metric columns like this? I have pivot tables with several metrics, so I would like to group them together. Ideally, I would like to do so without scripting 

dashboards & reporting

1 Reply

Replies have been turned off for this discussion
  • Angelina_QBeeQ's avatar
    Angelina_QBeeQ
    ETL
    09-13-2022

    Hi amelia ,
    You can achieve something similar by adding a small table. The idea is similar to this one (https://community.sisense.com/t5/build-analytics/transposing-pivot-table/m-p/2274), but you will add in the table "category" field like this:

    select 'Revenue' as measure, 1 as key,1 as id, 'group 1' as category
union all
select 'Quantity' as measure, 1 as key,2 as id, 'group 1' as category
union all
select 'Discount' as measure, 1 as key,3 as id, 'group 2' as category

    The formula is the same as in the article:

    case when avg([id])=1 then [Total Revenue]
     when avg([id])=2 then [Total Quantity]
     else [Total Discount]
end

    In the table, you will need to add "category" and "measure" fields as columns and you will get the following result:

    But this method is suitable when you do not have too many measures.

    Best regards,
    Angelina