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

Vaslo

Board Regular
Joined
Jun 3, 2009
Messages
156
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:

CustomerTotal Volume YTDTotal Margin YTD
A2501000
B123300
C00
D0-5
E100-100

<tbody>
</tbody>

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:
CustomerTotal Volume YTDTotal Margin YTD
A2501000
B123300
C0
D-5
E100-100

<tbody>
</tbody>

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()?
 

Some videos you may like

Excel Facts

Enter current date or time
Ctrl+: enters current time. Ctrl+; enters current date. Use Ctrl+: Ctrl+; Enter for current date & time.

Redoute

New Member
Joined
Nov 4, 2018
Messages
12
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.

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.
 

Watch MrExcel Video

Forum statistics

Threads
1,096,348
Messages
5,449,874
Members
405,578
Latest member
bbenny01

This Week's Hot Topics

Top