Question

Deleting or Hiding Selected Pivoted Dimension

  • 22 December 2017
  • 3 replies
  • 906 views

Hello,


I’ve pivoted a dimension, Sources of Revenue which corresponds to a Revenue measure. I also have a Row Total of Revenue, for Total Revenue of all of the Sources. I only want one of the Sources and the Total Revenue on my Visualization. Is there a way to delete or hide all of the other sources so I can still show these 2 measures (One selected Source and Total Revenue of all the Sources)?


Thank you!


3 replies

Userlevel 3

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

It was coming up with some incorrect values, but I changed Filtered Revenue to:

if(${view_name.source}=“Source 1”,${view_name.total_revenue},0)

and it worked.


Thanks for your help! 🙂

Userlevel 3

Good spot. Thanks! 🙂

Reply