Trimming Pivot Table to Only Display Items with Top Totals

afieweger
afieweger Member
in Charting

Hey there forum community.

Using the Pivot Table in domo and trying to trim the rows to only display the top "X" of them to cut out a lot of the noise of low value items. Wasn't able to find anything online. In excel pivot would be a simple filter.

Andy

Tagged:

Best Answers

  • swagner
    swagner Contributor
    Answer ✓

    @afieweger have you tried adding a card filter with an aggregation?

    filter.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Filtering pivot tables is often tricky, specially if you're expecting the data displayed to be responsive to filters on the page or dashboard level, and even more if you want to limit to a specific top "X". If a less restrictive approach is acceptable, you can build a BeastMode that tells you the actual total for each row at the level you want, and filter the table to only include those that are above a certain threshold, something like:

    SUM(SUM(`Views`) FIXED(BY `Page title`))

    Then you can use this as the Sorting and Filter of the Pivot Table:

    Untitled drawing (5).png

    While it won't limit it to the top X, you can rule out anything that you'd consider to be noise by setting up a threshold after which you do want to show things. In this case I did TotalViews >= 2000 as my filter.

    The reason you need a BeastMode with the fixed function is that otherwise the filter will apply to the level of the SUM of the fields, so in my case that the Quarterly views on each quarter, so it all depends on what you need.

Answers

  • swagner
    swagner Contributor
    Answer ✓

    @afieweger have you tried adding a card filter with an aggregation?

    filter.png

  • marcel_luthi
    marcel_luthi Coach
    Answer ✓

    Filtering pivot tables is often tricky, specially if you're expecting the data displayed to be responsive to filters on the page or dashboard level, and even more if you want to limit to a specific top "X". If a less restrictive approach is acceptable, you can build a BeastMode that tells you the actual total for each row at the level you want, and filter the table to only include those that are above a certain threshold, something like:

    SUM(SUM(`Views`) FIXED(BY `Page title`))

    Then you can use this as the Sorting and Filter of the Pivot Table:

    Untitled drawing (5).png

    While it won't limit it to the top X, you can rule out anything that you'd consider to be noise by setting up a threshold after which you do want to show things. In this case I did TotalViews >= 2000 as my filter.

    The reason you need a BeastMode with the fixed function is that otherwise the filter will apply to the level of the SUM of the fields, so in my case that the Quarterly views on each quarter, so it all depends on what you need.

Categories