is this the sort of thing you had in mind
I used rank (at first set to 5 but changed to 20
If you need to rank by year add the year(order date) before the colon in the LOD
the the Percent is
Or this if you want to avoid the table calc
If this posts assists in resolving the question, please mark it helpful or as the 'correct answer' if it resolves the question. This will help other users find the same answer/resolution. Thank you.
What I did in there:
Sheet 3. Just use the raw dimensions. Sheet 3(2): I made two different overall sales by state. One using LOD. One using table calcs. (They match.) (Ignore top-20 states on this one.)
Sheet (3): Set the table calc for Top-20 states to run along State. We get 1-through-N. I displayed it in measures, and I put it on filters to get top-20, and I made a copy of it as DISCRETE and put it as the first pill on ROWS so it sorts the selected states for us.
Sheet 4: I made a calc to rank sales within each state. Sheet 5: Put it on filters to get top-5. Made a copy discrete and put it before Sub-Category on ROWS to use it to sort.
Sheet 6. Made a series of calcs to give % within a state, % for all sales, % for each category.