I have a pivot Bar chart that is created from a pivot table which is created in Power Query and added to the data model.
The pivot table has just 2 columns "Item Name" as Rows and "Total Items sold" as a value.
I need to filter Total Items Sold for Greater than 0 AND get the bottom 10 of the result of the first filter.
I have tried enabling "Allow multiple filters for a field" but that does not work (the Greater Than filter is still cleared before the Bottom 10 gets filtered).
I can't do this in PQ because the user needs to choose whether or no to exclude the zero values from the Bottom 10 filter.
Any help or suggestions greatly appreciated. TIA
Please upload a file (exclude any confidential information) so that we can see what exactly you are dealing with.
The actual file pulls data from 10 other csv files in different folders. It also contains many PQs, I think it would be better if I was to create a separate file that replicates the issue.
Here is the sample file.
Data is in a Table rather than CSV files. There are just 4 Events and 30 Items as opposed to the real situation that is up to 35 Events over 5 years and approximately 900 Items per Event (not all events will have exactly the same Items.
Just 1 Slicer for Event and buttons to control filtering. In the actual file there are additional slicers for Category (Items are in different Categories) and Year plus button for Top 10 (that does not need zeros omitting).
I loaded the Table into PQ then loaded it back to the sheet as a Pivot Table and added that to the Data Model so all that is consistent with the actual file.
Thanks
Sorry uploaded an unfinished Sample File. Here is the finished version.
You could create a measure called ItemsSoldNoZero with the formula:
=if([Sum of Items sold]=0,blank(),[Sum of Items sold])
Then amend your button code to:
Sub Button3_Click()
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Table1].[Item Name].[Item Name]").ClearAllFilters
ActiveSheet.PivotTables("PivotTable1").PivotFields( _
"[Table1].[Item Name].[Item Name]").PivotFilters.Add2 Type:=xlBottomCount, _
DataField:=ActiveSheet.PivotTables("PivotTable1").CubeFields( _
"[Measures].[ItemsSoldNoZero]"), Value1:=10
End Sub
Thanks.
That works. It does add an additional bar to the chart which I have to make invisible.
You shouldn't need that measure actually in the chart at all.
You are right, of course. Thanks
