Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
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!
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))
))
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))
))
Hello. Thanks for the reply. But it's not working. Can you think of any other way maybe? TIA!
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))
))
Thanks a lot! How much difference a single comma makes! This works! You the real MVP!