7 Replies Latest reply on May 2, 2016 12:28 AM by Tableau kumar

# Table Calcs - Top 20 % products

Hi Guys,

In the attached workbook, I am trying to find out :

How much of profit (% of total profit) does top 20% of products by sales contribute to total profit?

I am able to find out top 20% of products by sales but I am getting stuck while calculating their profit contribution.

Do I need to get the 20% of total size and then put that in top n filter and then get a view on profit? Or there is a better way to do this?

Any help would be appreciated.

Thanks,

Rishabh

• ###### 1. Re: Table Calcs - Top 20 % products

[Profit] / {Fixed [All] : sum([Profit])}

where [All] is a Calculated field with Value 'All' or any other placeholder

Unfortunately I couldn't open your dashboard as I am in the office and we are still on 9.2.4
If you need further assistance I can get back to you after work from my personal laptop

• ###### 2. Re: Table Calcs - Top 20 % products

I'm assuming you are using the Sample superstore data

• ###### 3. Re: Table Calcs - Top 20 % products

Yes Khalid, I am using sample superstore data. I will try your solution and let you know if it helps.

Thanks,

Rishabh

• ###### 4. Re: Table Calcs - Top 20 % products

hi Rishabh,

So one way to do this is to use the RANK_PERCENTILE. Although as we'll need [Product Name] in the VizLoD (so the Table Calculation can run over that product name level. This creates a little bit of complexity, but we can get round it. In the attached there is a 'How it works' tab, which shows the builld up of the formulas so you can see what each is doing, before we get to the final solution. I've also split the various formulas up here, for explanatory purposes, but you can nest them into 1 or 2 in you final solution (if you wish)

So first thing to do is get the Top 20% of products by Sales

[Percentile Sales]

RANK_PERCENTILE(SUM([Sales]))

Then we can grab just the Top 20 of these products and get their Profit

[Top 20 % Profit]

IIF([Percentile Sales]>=0.8,SUM([Profit]),0)

Noe this gives us the profit, populated for each of the Top 20% of products, but we want just one figure (the SUM of these) so we use WINDOW_SUM

[Top 20 % Profit Window_Sum]

WINDOW_SUM([Top 20 % Profit])

[Total Profit]

TOTAL(SUM([Profit]))

So we now have all the measures we need...and create the %age of Profit through the Top 20% of products

[Top 20% Sales %age of Total Profit]

[Top 20 % Profit Window_Sum]/[Total Profit]

I've then put the product name into the detail shelf (as we need this in the VizLoD for the above calculations to work), which you can see in the 'Final Solution_Almost' tab.

However we now have the problem, of the correct result, but repeated for each product...so we can fix this with the nifty addition of an IF FIRST()=0..END

[Top 20% Sales %age of Total Profit - Only Return 1]

IF FIRST()=0 THEN [Top 20 % Profit Window_Sum]/[Total Profit]END

So this returns the value for the 1st product, and the rest are NULL, so don't get plotted

Then one final thing, else we get odd column widths is to turn off the stack marks

Hope this helps and makes sense, but please post back if not (on either count!)

• ###### 5. Re: Table Calcs - Top 20 % products

Great let me know if it works.

• ###### 6. Re: Table Calcs - Top 20 % products

Hi Simon,

This is great, exactly what I was looking for. I think I was missing the trick of using Rank_Percentile calculation which actually opened the doors.

I have nested the calculation in my final solution as :

WINDOW_SUM(IF [Rank Sales]>=0.8 then sum(Profit) else 0 end)/TOTAL(sum([Profit]))

Thanks for your help. Much appreciated.

Regards,

Rishabh