I created a pivot table (screenshot below). I couldn’t sort the table by the column “Total Fees”. How can I sort this column?
Hi Keddy! Click on the field wells for Mask_org and choose to sort that by Total Fees. By default if you use the sort on the measure field itself it will sort the inner most dimension. You need to sort the higher level dimensions explicitly using the field wells.
Thank you Jesse!
Does it mean if I click on the field wells for Mask_Org and sort that by Total Fees, it will sort the column total of Week 2 and Week 3’s total fees?
I have another column “Diff Fees” (Week 3 Fees - Week 2 Fees) and the formula is difference(sumOver(sum(Fees),[{Mask_Org},{Mask_Location},{week number},year]),[year ASC],-1,[{Mask_Org},{Mask_Location}])
It’s showing blank in the column total (screen shot below) do you know how to fix this? I actually want to sort by Diff Fees.
“Does it mean if I click on the field wells for Mask_Org and sort that by Total Fees, it will sort the column total of Week 2 and Week 3’s total fees?” – Correct
Yes some types of Table Calculations dont support subtotals/totals at this time (this looks like 2 nested table calcs)…
I might suggest doing the 3 columns (week 2, week 3 and diff) a different way. The only downside with this approach is the column headers would have to be generic (like ‘This week’, ‘Last Week’ rather than the specific week number).
It would be really easy to sort if you do it that way, and the report will look better (wont have the empty Diff column for the first period)
1 Like