Results 1 to 2 of 2

Thread: PowerPivot FILTER() not working as expected for filtering Pivot Table
Thanks Thanks: 0 Likes Likes: 0

  1. #1
    Board Regular
    Join Date
    Jun 2009
    Location
    Pittsburgh
    Posts
    155
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default PowerPivot FILTER() not working as expected for filtering Pivot Table

    I have a table that has a bunch of =SUM()/CALCULATE Calculated Fields. Items like:

    Code:
    Total Gallons:=SUM(MasterData[Net Quantity in GAL])
    Code:
    Total Gallons YTD:=CALCULATE ( [Total Gallons], DATESYTD ( MonthOnlyDateTable[Date] ) )
    etc. for the usual financial metrics

    I created a pivot table to list to provide Top 10 reports by Business Segment. I get results for one segment like below when I sort on Margin:

    Customer Total Volume YTD Total Margin YTD
    A 250 1000
    B 123 300
    C 0 0
    D 0 -5
    E 100 -100

    What I would like to do is to remove all rows from the table where volume is 0 (so only customers A,B, and E remain). To test this I tried using FILTER() in the Total Volume to just eliminate everything less than 0 to start with:

    Code:
    Total Volume YTD:=CALCULATE ( [Total Gallons], DATESYTD ( MonthOnlyDateTable[Date] ) , FILTER(MyTable[Total Volume YTD]<0))
    But I get this result of just getting a blank instead of losing the line:
    Customer Total Volume YTD Total Margin YTD
    A 250 1000
    B 123 300
    C 0
    D -5
    E 100 -100

    What is the best way to omit results from a Pivot Table based on excluding items according to one column?


    Bonus question: Should I wrap my SUM() items in a CALCULATE()?

  2. #2
    New Member
    Join Date
    Nov 2018
    Posts
    9
    Post Thanks / Like
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Default Re: PowerPivot FILTER() not working as expected for filtering Pivot Table

    Quote Originally Posted by Vaslo View Post
    What is the best way to omit results from a Pivot Table based on excluding items according to one column?
    I think you have to constrain each other measure like this:

    Code:
    Total Margin YTD:=IF(ISBLANK([Total Volume YTD], BLANK(), ...)
    When all measures in a pivot table row return blank, then Excel will hide that row.

    Quote Originally Posted by Vaslo View Post
    Bonus question: Should I wrap my SUM() items in a CALCULATE()?
    I think no. CALCULATE is all about modifying or creating filter context, it is not needed in simple measures.

    Disclaimer: I'm a beginner with Power Pivot/DAX.

Some videos you may like

User Tag List

Tags for this Thread

Like this thread? Share it with others

Like this thread? Share it with others

Posting Permissions

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