+ Reply to Thread
Results 1 to 6 of 6

Pivot table filter value with greater than x slicer

  1. #1
    Registered User
    Join Date
    04-29-2020
    Location
    Germany
    MS-Off Ver
    Office 16
    Posts
    3

    Pivot table filter value with greater than x slicer

    Hi all,



    I have a somewhat large table (close to 1M rows) that I'm trying to analyze using a Pivot Table. The rows section shows me the customer ID and the values are the project volume amongst other things. I created a really nice looking interactive dashboard and i was was wondering if it was possible to use slicers to filter a pivot table by any of the value filters?



    My first approach was the following:

    I created a helper table on another sheet, which simply contains a series of values (starting from 0 to 500k in 100k steps) and a name. The value names are simply used for the slicer for aesthetic reasons. For the helper table I created a slicer so that I can filter the values.

    Then I created a measure in the actual customer table, which gives me the MIN value from the helper table
    HTML Code: 
    Min_Param_X =MIN(ParameterX[Param_X])
    and a second measure that only shows values if greater than the MIN
    HTML Code: 
    Project_volume_GreaterThanX
    
    =IF(
    [Project_volume]>=[Min_Param_X],
    [Project_volume],
    0
    )
    Two problems with this approach:
    1. the MIN function shows me the MIN value of the helper table not only for the visible values (even if it is filtered, the MIN value remains 0)

    2. the actual table does not react and does not update itself from the helper table after using the silcer

    Can anyone tell me if this is generally possible? Where is my thinking error? Is there possibly a smarter approach to reach the goal?


    I am grateful for any advice

  2. #2
    Forum Guru Pepe Le Mokko's Avatar
    Join Date
    05-14-2009
    Location
    Belgium
    MS-Off Ver
    Excel 2010 on Linux
    Posts
    11,933

    Re: Pivot table filter value with greater than x slicer

    Administrative note

    Welcome to the forum

    in your haste to solve your problem, you probably missed the yellow banner advising how to get answers faster by posting a sheet ?

    Please take a moment to read it and attach a sheet accordingly.

    Thanks you for helping us help you

  3. #3
    Registered User
    Join Date
    04-29-2020
    Location
    Germany
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Pivot table filter value with greater than x slicer

    Hi,


    Thank you very much for your answer.



    I have attached a sample file that shows my basic problem. Again I try to use the table below to create a way to filter the projects in the actual table using the silcer.*



    Please give me feedback if something is not clear. I hope for some advice.*



    Thanks and best regards
    Attached Files Attached Files

  4. #4
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,854

    Re: Pivot table filter value with greater than x slicer

    Hello ma.har and Welcome to Excel Forum.
    The tutorial linked below may describe what you are looking for.
    https://powerpivotpro.com/2011/09/gu...tween-slicers/
    Consider taking the time to add to the reputation of everybody that has taken the time to respond to your query.

  5. #5
    Registered User
    Join Date
    04-29-2020
    Location
    Germany
    MS-Off Ver
    Office 16
    Posts
    3

    Re: Pivot table filter value with greater than x slicer

    Quote Originally Posted by JeteMc View Post
    Hello ma.har and Welcome to Excel Forum.
    The tutorial linked below may describe what you are looking for.
    Hi JeteMC,

    I completely agree with you! This is exactly what I tried to do at the beginning, but it led to the problem mentioned above.

    -> the MIN function in the DAX formula shows me not only the MIN value of the filtered (visible) values but of the total existing values in this table (the result is therefore always the smallest existing value ->0)

    Do you have a solution to this? The attached sample file allows you to recreate my problem.


    Thank you and best regards

  6. #6
    Forum Guru
    Join Date
    08-28-2014
    Location
    USA
    MS-Off Ver
    Excel 2010/2019
    Posts
    11,854

    Re: Pivot table filter value with greater than x slicer

    the MIN function in the DAX formula shows me not only the MIN value of the filtered (visible) values but of the total existing values in this table
    I ran across something that made me think about how this might be done.
    This video tutorial talks about that DAX Calculate function which should allow you to filter.
    https://www.youtube.com/watch?time_c...ature=emb_logo
    Let us know if you have any questions.

+ Reply to Thread

Thread Information

Users Browsing this Thread

There are currently 1 users browsing this thread. (0 members and 1 guests)

Similar Threads

  1. Replies: 6
    Last Post: 12-18-2017, 05:04 PM
  2. Filter a 2nd pivot with the inverse of a slicer
    By spikey_plant in forum Excel Charting & Pivots
    Replies: 0
    Last Post: 06-27-2015, 12:55 PM
  3. Replies: 0
    Last Post: 03-20-2015, 11:39 AM
  4. Replies: 2
    Last Post: 07-30-2014, 05:50 AM
  5. Updating a pivot filter with another pivot updated by a Slicer
    By Jabba69 in forum Excel Programming / VBA / Macros
    Replies: 5
    Last Post: 07-17-2014, 04:08 PM
  6. Greater than or equal to filter in Pivot table
    By Jonathan9 in forum Excel Charting & Pivots
    Replies: 1
    Last Post: 09-24-2013, 12:27 PM
  7. [SOLVED] Use slicer selections on one pivot table to filter another pivot table
    By porkandbeans in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 04-06-2013, 12:30 AM

Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts

Search Engine Friendly URLs by vBSEO 3.6.0 RC 1