Deleting or Hiding Selected Pivoted Dimension

Hi

This is possible in two different ways:

Modelling

You can create a new filtered measure which is something like (assuming you have a revenue dimension which you sum to get the total_revenue measure)

measure: total_revenue {

    type: sum

    sql: ${revenue}

}



measure: total_revenue_by_source_1 {

    type: sum

    sql: ${revenue};;

    filters: {

        field: source

        value: "source_1"

    }

}

Then you can add these two fields to a report, to get the required values.

Table Calculations

This is also possible using Table Calculations during report creation, after you’ve pivoted by Source, and added Total Revenue and the Row Total.

Create three new calculated fields:

1. Filtered Revenue: sum(if(${view_name.source}="Source 1",${view_name.total_revenue},0))
   


2. Total Revenue by Source 1: sum(pivot_row(${filtered_revenue}))
   


3. Total Revenue all Sources: ${view_name.total_revenue:row_total}
   

Then you can hide the columns ‘Total Revenue’, and ‘Filtered Revenue’, to give you the report you are looking for.

Andy

Good spot. Thanks! 🙂