Looker Connect Training

Help Center

Documentation

Community

Cloud Certifications

Question

Deleting or Hiding Selected Pivoted Dimension

  • 22 December 2017
  • 3 replies
  • 1035 views
J

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

powellandy
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

J

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! 🙂

powellandy
Userlevel 3

Good spot. Thanks! 🙂

Reply


Terms of Service