11 Replies Latest reply on Dec 22, 2018 6:45 PM by Joshua Preston

Sort by % of total table calc

I think this is straight forward. I just can't create a calc field that does the trick and I've checked the forums to no avail.

I'd like the women % percent of total sorted in descending order. twbx 2018.1 attached.

Can the community come to my rescue? thx!

• 1. Re: Sort by % of total table calc

Hey Josh

Have you tried to duplicated your green pill to the rows shelf, then double click on it and add a - at the front, next right click it and make it a Dimension. Finally move it to the left of the Location.

This will make the list sorted in descending order by your % total

At the end just hide the field you created and you are done!

Let me know if that helps

Rodrigo

• 2. Re: Sort by % of total table calc

Hey Rodrigo! I did try this but it didn't work. Here's the result. I'm at a loss.

• 4. Re: Sort by % of total table calc

Could you share the workbook?

• 5. Re: Sort by % of total table calc

What if you move the CNT(Location) to the right of the Location pill?

• 6. Re: Sort by % of total table calc

There was an error on my original response. You need to make the field Discrete not a Dimension.

But regardless I believe you need to lock your table calculation before duplicating it. As you added a new field in the view that affects the Table Calc too.

To lock the table calculation, you need to edit the table calculation and click "Specific Dimensions" after you get the calculation you need.

Below a similar example using Sample Superstore. I hope it helps.

• 7. Re: Sort by % of total table calc

Thx Rodrigo. These are very clear instructions. I walked through it and locked in the table calc and only left the "Winner ***" dimension. That's what's tripping me up. But if I remove that (and adjust the calc accordingly) I get the below view. Workbook is attached at the top of the thread with the "real" view I'm trying to sort.

• 8. Re: Sort by % of total table calc

Hi Joshua,

I was able to get your sort working by created a calculated field called "Woman Sort":

{FIXED [Location]: SUM(if [Winner ***] = 'Woman' THEN [Number of Records] END)}

/

{FIXED [Location]: SUM([Number of Records])}

I then sorted your location by Woman Sort descending and this is the result.

Workbook attached.

Best,

Paul

• 9. Re: Sort by % of total table calc

Looks like the forums don't like the second word of your dimension field and replaced it with three stars   The attached workbook has the working calculation.

• 10. Re: Sort by % of total table calc

Oh boy.. sorry Josh.. I am new here I didnt see the workbook at the bottom. I am glad you got your answer

• 11. Re: Sort by % of total table calc

Thx so much Paul. I appreciate the LOD calc. It worked beautifully. I was trying to find one that worked earlier, but I need to improve my syntax skills.

(And I've seen the s-e-x word filtered before)

(Rodrigo - thx for the very useful trick for table calc sorting. I'll keep it handy). cheers!