
1. Re: Table Calcs  Top 20 % products
khalid norat Apr 29, 2016 2:30 AM (in response to Rishabh Dhingra)[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
khalid norat Apr 29, 2016 2:31 AM (in response to khalid norat)I'm assuming you are using the Sample superstore data

3. Re: Table Calcs  Top 20 % products
Rishabh Dhingra Apr 29, 2016 2:37 AM (in response to khalid norat)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
Simon Runc Apr 29, 2016 2:39 AM (in response to Rishabh Dhingra)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])
We also need the total profit for all products
[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!)

Top 20% Products  SR.twbx 514.8 KB


5. Re: Table Calcs  Top 20 % products
khalid norat Apr 29, 2016 3:13 AM (in response to Rishabh Dhingra)Great let me know if it works.

6. Re: Table Calcs  Top 20 % products
Rishabh Dhingra May 1, 2016 11:58 PM (in response to Simon Runc)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