Create a Calculated Pareto Dimension for a Pie Chart

[mrthomasshelby]

Hello all! I have a dataset that is as follows:

Vendor       Sales    Type

A              1300        C

B              2200        N

A              1400        N

B              2100        N

C              2500        C

A              300          N

C              900          C

D              3200        N

B              2000        C

D              1500        C

E              1100        N

F              3500        C

E              1500        C

G              1400        N

H              600          C

H              900          N

G             1200         C

Now I would like to create a Calculated dimension to use in a Pie chart. The Pie chart should have the Dimensions as 'C' and 'N Top 80%'. To further break it down, I'd like to show the % of Sales coming under the Type C and % of Sales coming under the Top 80%(Pareto) Vendors in Type N. Can you please help me create a calculated dimension to achieve this? TIA!

[mrthomasshelby]

No. I would like to display the same pie chart but this time only for the data which comes under Bottom 20% Pareto in Type1='M'. Thanks!

[sunny_talwar]

Would you be able to provide the expected output just like you did the last time.

[mrthomasshelby]

1.I'd like to perform a Pareto analysis on the above dataset with Type1='M'. So the data set reduces to:

Vendor       Sales    Type    Type1

A              1300        C           M

A              1400        N           M

B              2100        N           M

A              300          N           M

C              900          C           M

D              3200        N           M

F              3500        C           M

E              1500        C          M

H              600          C         M

G             1200         C         M

If you perform Pareto analysis on this data set, you'll see that Vendors C,E,G and H fall under the Bottom 20% Category. So, our data set now will be:

C              900          C           M

E              1500        C          M

H              600          C         M

G             1200         C         M

Now on this data set I'd like to show the above mentioned pie chart with split according to 'C' or 'Top 80% N' In this case, it should display 100% Under 'C'.

[sunny_talwar]

Something like this

Pick(ValueList(1, 2),

Sum(Aggr(If(Rangesum(Above(

Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales)/

Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} TOTAL Sales), 0,RowNo())) <= 0.8,

Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>}Sales)),

(Vendor,(=Sum({<Vendor= {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'N'}, Type1 = {'M'}>} Sales),Desc)))),

Sum({<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'C'}, Type1 = {'M'}>}Sales))

[mrthomasshelby]

Whoa! That looks so complicated! But it works I guess! Thanks a lot again! It's going to take a while for me to figure out how this expression works I guess! Thanks a lot Sunny! I have learnt something new and really useful again!

[sunny_talwar]

I guess breaking it down might help...where I have repeated this set analysis multiple times

{<Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}, Type = {'C'}, Type1 = {'M'}>}

and the important part was that you only wanted vendors which were bottom 20% in Type1, which is this part

Vendor = {"=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc))) > 0"}

To see how this behave, create a table with Vendor as dimension and this expression

=Aggr(If(Rangesum(Above(Sum({<Vendor, Type1 = {'M'}>} Sales)/Sum({<Vendor, Type1 = {'M'}>} TOTAL Sales),0,RowNo()))>0.8, Sum({<Type1 = {'M'}>}Sales)), (Vendor,(=Sum({<Vendor, Type1 = {'M'}>} Sales),Desc)))

You will only see C,E,H,G have value greater than 0.

[sreelathamohan]

Hello Sunny,

I have a similar requirement just that mine is a bar chart and I want to show all the bars with their respective dimension values that contribute to top 80% and aggregate 81%-100% as "Others" for the last dimension bar. Is this possible? Any help would be greatly appreciated. Please see attached for an example of how my bar chart should look like.

Thanks.