In the first example, the filter is from the dimension of the secondary datasource. As a result the data from the first datasource is not filtering for sorting. You can see this by just removing the state of California from the filter shelf on Coffee Chain sort and you will see that the sort is correct for no filter.
So - some options exists.
1 - sort based on the local state field for the current data source. Just add the Coffe Chain State field to the filters shelf and you will see the sort fixes itself.
2 - take a discrete version of the measure "Sum(Sales)" and place to the left of [Product] on the rows shelf. Then sort on this field. You can even hide this field by turning off the "Show Header" from the drop down menu. This is the quick method to fix the issue.
1 of 1 people found this helpful
I incurred the same issue in my report and what worked for me was to add all of the filters from the secondary data source to context (turning the pills gray).
Thank you so much mate ; I switched context from Primary to Secondary Source Filters (Related filters) and sorting is working as expected.