8 Replies Latest reply on Jan 31, 2018 1:09 PM by Peter Fakan

# Using LoD Calc for Percentage of Total

Howdy All!

I feel like I am on the verge of getting this but just not the best at LoD calc's.

I am doing a visual where I need to rank top 5 of regions per region, as below.

My issue is that for the label I want it to portray the % of total for only that region. So, of the 5 items showing up for Central, the 5 items should add up to 100%. And continue on for other regions adding up to 100%.

{ FIXED [Region], [Sub-Category] : SUM([Sales])}

And then toss on labels and use a quick calculation of "Percent of Total".

However this did not work out at the entire visual still adds up to 100, not the individual regions

Any solutions by chance?

Thanks! Dashboard attached is superstore data from Tableau 10.3

• ###### 1. Re: Using LoD Calc for Percentage of Total

Hi Jonathan,

Is this what you are looking for ?

Just change your sum(sales) to a table calc, and set it to partition across region

HTH 10.5 attached

Peter

• ###### 2. Re: Using LoD Calc for Percentage of Total

Howdy Peter!

I think it's so close but not quite. So if I do what you mention the totals for regions don't quite add up to 100%. With the change I get the following, which add up to 111.

• ###### 3. Re: Using LoD Calc for Percentage of Total

Hi Jonathan,

I've converted the inbuilt calc to a LOD, showing working. This gives the percentage across region, but not as a percentage across the top 5.

I'm not quite sure how to recalculate the sum once you have the index on filter though.

HTH 10.5 attached

Peter

• ###### 4. Re: Using LoD Calc for Percentage of Total

Hi Peter/ Others

would it  also be possible to track groups overtime? for example:

i want to know from the 100 customers that started 4 years ago (2015) how much % came back in 2016 (40/100), 2017 (30/100) and in 2018 (28/100) and then per group. Thus also for 3 years ago 60/120 and 30/120 etc etc.

• ###### 5. Re: Using LoD Calc for Percentage of Total

Hi Jonathan (and Peter) -

I'm not sure you need an LOD for this.  Seems like putting this calculated field sum(Sales)/total(sum(Sales)) on your Text shelf for the label and then setting the table calc to calculate using Sub-Category would get you the %s within each region for your top 5 (or however many you end up showing).  I don't have 10.5 yet (boo!) so wasn't able to open your workbook, but I tried out this calc by recreating your viz in SuperStore as best I could from the screen shots and descriptions, and it seems to be working.

Best,

Jen

• ###### 6. Re: Using LoD Calc for Percentage of Total

Howdy Jen!

Thanks so much for looking into this! If anything it's 10.3 and not 10.5 quite yet that I have attached ;P But I am excited about upgrading to 10.5 here soon.

I tried your recommendation and maybe I goofed up (highly possible) but it still didn't come out quite right?

My blue region only adds up to 58 still? I think Peter was onto something where it is not taking my index of 5 into account.

• ###### 7. Re: Using LoD Calc for Percentage of Total

Ah, I see.  And yes, I also see that you said you're on version 10.3.  I read Peter's reply with his 10.5 workbook and that stuck in my head.  (Note to self:  develop better reading comprehension skills.)

Anyway, I also had trouble figuring out how to make this work with Index.  So I tried Rank_unique instead, and with Ivan Young's  strategy in this post (How to show % of total on Top N parameter ) I think it is actually working now.  Sorry to say I cannot explain why this didn't work for me when I used the exact same technique with Index instead of Rank, but that mystery gives me something else to figure out.

Hopefully this strategy works for you, too!

Hopefully the screen shots are sufficient since you won't be able to open my 10.4.2 workbook until you upgrade, but I attached it anyway in case it helps someone else.

Jen

• ###### 8. Re: Using LoD Calc for Percentage of Total

I prefer rank over index for these calcs too