4 Replies Latest reply: May 20, 2017 9:58 AM by Phalgun Parvathaneni

# Sum of Sales of Vendors Contributing to 80% of Sales

Hello, I have the following data:

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'd like to Sum up the Sales value of the Vendors of type 'C' contributing to Top 80% of the sales. Can you please help me with the expression for the measure? stalwar1 loveisfail I have tried something like this:

Sum({<Type={'C'} Aggr(

Rangesum(Above(Sum(Sales)/Sum(total Sales),1,RowNo()))<=0.8

(Vendor,(=Sum(Sales),Desc))

))

But it doesn't seem to be right and doesn't work. Thanks in advance!

• ###### Re: Sum of Sales of Vendors Contributing to 80% of Sales

Maybe like

Sum({<Type={'C'} >} Aggr(

If( Rangesum(Above(Sum({<Type={'C'} >} Sales)/Sum({<Type={'C'} >} total Sales),1,RowNo()))<=0.8,

Sum({<Type={'C'} >}Sales)

)

(Vendor,(=Sum({<Type={'C'} >}Sales),Desc))

))

• ###### Re: Sum of Sales of Vendors Contributing to 80% of Sales

Hello. Thanks for the reply. But it's not working. Can you think of any other way maybe? TIA!

• ###### Re: Sum of Sales of Vendors Contributing to 80% of Sales

It's not working does exactely mean what? No result, wrong result, computer exploded?

I hope you checked the expression, there is a comma missing before the aggr() dimensions:

Sum({<Type={'C'} >} Aggr(

If( Rangesum(Above(Sum({<Type={'C'} >} Sales)/Sum({<Type={'C'} >} total Sales),1,RowNo()))<=0.8,

Sum({<Type={'C'} >}Sales)

)

,

(Vendor,(=Sum({<Type={'C'} >}Sales),Desc))

))

• ###### Re: Sum of Sales of Vendors Contributing to 80% of Sales

Thanks a lot! How much difference a single comma makes! This works! You the real MVP!