Sum of Sales of Vendors Contributing to 80% of Sales

[mrthomasshelby]

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!

[swuehl]

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))

  ))

[mrthomasshelby]

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

[swuehl · Accepted solution]

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))

  ))

[mrthomasshelby]

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