Top Vendor with cumulative SUM

[Sara_3]

I have a table :

test:

vendor name,

Sales Value,

6 Months Sales Value

from QVD;

what I have :

Vendor Name	6 Months Sales Value (Total = 9000)
B	3000
A	5000
C	1000

What I need : for eg : if vendor A [Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8

Top Vendor Name	6 Months Sales Value
A	5000

I only need top Vendors Name in the table which have :

[Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"

Requirement: is it possible to do all in load script to get top vendors name?

1- Look at Vendor Name in the last 6 months and calculate the total (9000) at the Vendor level, order that table from high to low. (front end)

2- Then create a cumulative sum (high to low depend on 6months Sales value)(so Vendor#1 shows Vendor 1 (6 Months Sales Value), Vendor  #2 shows Vendor  #1 AND Vendor #2 (6 Months Sales Value), and so on).

Whenever [Cumulative Sum] (high to low depend on 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"

Sum(Total [Cumulative Sum]) === [Cumulative Sum] (high to low depend on 6months Sales value)/ Total(9000)

[Kushal_Chawda · Accepted solution]

@Sara_3  try below

=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [6 Months Sales Value])

[Sara_3]

Thanks Kushal , I am going to try today and let you know .

Really Appreciated!

[Sara_3]

Kushal I tried your above logic it gives me numeric value , I need top Vendors names in my table who has under this criteria :

[Cumulative Sum] (Decending 6months Sales value)< Sum(Total [Cumulative Sum])*.8 then [Top Supplier] = "Y"/ OR  "top vendors names"

My output should be:

Top Vendors Name ([Cumulative Sum] < Sum(Total [Cumulative Sum])*.8	6 Months Sales Value (higher to lower value)
D	5000
A	3500
B	1800

Thanks,

Beenish.

[Kushal_Chawda]

@Sara_3 it gives numeric value but that numeric values is for top vendor.  You need to Use this measure in chart where dimension is Vendor

[Sara_3]

oh ok, now I added this measure with my Vendor Name dimension. it gives some vendors has 6 Months sale values in front of vendor and gives some of them 0  value means these are not top vendors under my criteria right?????

[Kushal_Chawda · Accepted solution]

@Sara_3  Yes. you can suppress the zero values from Add-Ons option

[Sara_3]

Thanks!

[Sara_3]

Hi Kushal,

its working , but what i have to do when I am going to add 1 more column which is [Future 6 months sales value](field already exists) want to  comparison with last 6 months.  How to modify the BELOW expression for Future 6 Months sales without changing the Top vendors and last 6 months sales value (high to low) :

=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [6 Months Sales Value])

 output :

Top Vendors name	6 Months  sales value (HIGH TO LOW)	Future 6 months sales value
D	5000
A	3500
B	1800

[Kushal_Chawda]

@Sara_3  try below

=sum({<Vendor={"=sum(aggr(rangesum(above(Sum([6 Months Sales Value]),0,RowNo())),(Vendor,(=Sum([6 Months Sales Value]),DESCENDING))))/ sum(total [6 Months Sales Value])< 0.8"}>} [Future 6 months sales value])