+ Reply to Thread
Results 1 to 4 of 4

Power Pivot showing all rows of one table but only returning filtered results from another

  1. #1
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Power Pivot showing all rows of one table but only returning filtered results from another

    Hi,

    It's my usual row / filter context conundrum, essentially two related tables.

    I want the entire category to show in the pivot table and the related 'sales' figures from another table, not all of category have sales
    so when dropped in they are filtered out and I like to still be able to see them, I've attached a workbook with 3 simple tables illustrating the point;

    I've tried using ALL function as in

    < measure:= CALCULATE(SUM(SalesTable[Sales]),ALL(RepTable[Sales Rep])) >

    which of course doesn't work as it's sending the entire 'sales rep' to calculate. But it does give a full columns which can
    be dropped in to sort of achieve what I want , I've saved a table showing this. I've tried using the ALL, Keepfilters and other's but am a bit stuck.

    Richard.
    Attached Files Attached Files

  2. #2
    Forum Guru Bo_Ry's Avatar
    Join Date
    09-10-2018
    Location
    Thailand
    MS-Off Ver
    MS 365
    Posts
    7,211

    Re: Power Pivot showing all rows of one table but only returning filtered results from ano

    May put 0

    =Sum(SalesTable[Sales])+0

    or put null String

    =Var S = SUM(SalesTable[Sales])
    Return if(S,S,"")
    Attached Files Attached Files

  3. #3
    Forum Contributor
    Join Date
    08-03-2021
    Location
    London
    MS-Off Ver
    365
    Posts
    594

    Re: Power Pivot showing all rows of one table but only returning filtered results from ano

    Thanks BoRy,

    The string method is better, and return exactly what I was after, do you know if it's possible to do uisng a filter calcualte filter combination,
    I've tried ALL , Keepfilter, value, and nested filter in various combinations?
    I'll leave this open for a day and see if there are other annswers but thanks, for really nice straightforward solution.
    Richard.

  4. #4
    Forum Expert rorya's Avatar
    Join Date
    08-13-2008
    Location
    East Sussex, UK
    MS-Off Ver
    365 Ent SAC
    Posts
    8,887

    Re: Power Pivot showing all rows of one table but only returning filtered results from ano

    You could use:

    =CALCULATE(SUM(SalesTable[Sales]),ALL(SalesTable[Rep Id]))

    Also, I'd put your measures in the sales table personally.
    Rory

+ 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. Showing error while creating Pivot table in power query
    By rithamworld in forum Excel Programming / VBA / Macros
    Replies: 1
    Last Post: 10-16-2021, 06:40 PM
  2. Histogram on filtered table, results only on filtered rows
    By Cheesecube in forum Excel Charting & Pivots
    Replies: 9
    Last Post: 03-25-2018, 03:54 AM
  3. [SOLVED] Power Query: Extract data from table after other rows are filtered
    By JimDandy in forum Excel Formulas & Functions
    Replies: 8
    Last Post: 08-25-2017, 02:10 AM
  4. Filtered data showing when pivot table copy and pasted over
    By Simone Fick in forum Excel Programming / VBA / Macros
    Replies: 0
    Last Post: 08-07-2017, 07:30 AM
  5. Replies: 4
    Last Post: 06-19-2014, 12:59 PM
  6. Pivot table not showing filtered results
    By Dan.Reynolds in forum Excel Charting & Pivots
    Replies: 3
    Last Post: 05-28-2013, 02:09 PM
  7. Adding and showing new data in a filtered Pivot Table
    By andreamazzai in forum Excel Charting & Pivots
    Replies: 2
    Last Post: 01-10-2013, 07:20 PM

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