I have a table that has a bunch of =SUM()/CALCULATE Calculated Fields. Items like:
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:
<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:
But I get this result of just getting a blank instead of losing the line:
<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()?
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 |
<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:
Customer | Total Volume YTD | Total Margin YTD |
A | 250 | 1000 |
B | 123 | 300 |
C | 0 | |
D | -5 | |
E | 100 | -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()?