You can try below approach:
1. Select Sub-Category = LOOKUP(MIN([Sub-Category]),0)
2. Sub-Category Sales = WINDOW_AVG(SUM([Sales]))
Use "Select Sub-Category" as a filter.
I have un-checked "Art" but still the values are same.
For details there is a wonderful article, that you can refer :
WINDOW_AVG(expression, [start, end])
Returns the average of the expression within the window. The window is defined by means of offsets from the current row. Use FIRST()+n and LAST()-n for offsets from the first or last row in the partition. If the start and end are omitted, the entire partition is used.
For example, the view below shows quarterly sales. A window average within the Date partition returns the average sales across all dates.
WINDOW_AVG(SUM([Profit]), FIRST()+1, 0)computes the average of SUM(Profit) from the second row to the current row.
May be you can give an example ?
Please mark the answer as CORRECT & HELPFUL if it really helps you so that it can help others as well