Sum Function

Question

Sum Function

tygun

SSCrazy

Points: 2381
More actions
May 20, 2004 at 7:08 am

#108052

I am trying to utilize the Sum() function to create a calculated member tha ultimately gives me the percent of the total count.
Heres a simplified scenario
I have a Measure called Product Group. Lets say I have 3 products
apples, oranges, coconuts
Now lets say I have a measure that counts the orders for these so the data would look like this
Product                       Count of orders
All                                  100
Oranges                            30
Apples                              50
Coconuts                           20
I want a calculated member that would give me the percentage of orders that each product represents (i.e. the column next to the oranges count would be 30% becasue 30 orders out of 100 is 30%, and so on). I thought this would be a piece of cake, but I guess I'm not that smart.
Can someone please help me with this.
Thanks in advance, Scott

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

garfield_75025 SSC Veteran Points: 281 More actions · Answer 1

How about something like this.

SELECT Product, SUM([Count of orders]), CONVERT(FLOAT, SUM([Count of Orders])) / (SELECT CONVERT(FLOAT, SUM([Count of Orders])) FROM [Product Group]) FROM [Product Group] GROUP BY Proudct UNION SELECT 'ALL', SUM([Count of Orders], 1 FROM [Product Group]

Hope this helps,

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 2

Try this out. You have to declare the sum total as a decimal as it will not work as an integer for some reason.

create table test_prods

(name varchar(20), amount int)

insert into test_prods values

('pear', 100)

('orange',50)

('banana', 30)

('apple',20)

declare @sumtotal as decimal

select @sumtotal = sum(amount) from test_prods

select name, amount, amount/@sumtotal*100 as percentage

from test_prods

------------------------------
The Users are always right - when I'm not wrong!

Calvin Lawson SSChampion Points: 11030 More actions · Answer 3

Alternatively, you could put this in a trigger:

if object_ID('temp_Product') is not null drop table Temp_product

create table TEMP_Product

(Product varchar(255),

ProductCount int,

ProductPerc money

)

go

create Trigger TR_TEMP_Product

on TEMP_Product

after insert

as

Update TEMP_Product

set ProductPerc = cast(ProductCount as money)/(select sum(ProductCount) From TEMP_Product)

go

Insert temp_Product (Product, ProductCount) Values ('Oranges', 30)

select * from temp_Product

Insert temp_Product (Product, ProductCount) Values ('Apples', 50)

select * from temp_Product

Insert temp_Product (Product, ProductCount) Values ('Coconuts', 20)

select * from temp_Product

Signature is NULL

tygun SSCrazy Points: 2381 More actions · Answer 4

Guys, it looks like all of your suggestions are done outside Analysis Services. Remember I'm a little new to this so I'm not 100% sure why I can't do this in AS.

The first response looks like it may be utilized inside of AS. Would this script be put into a Calculated measure?

Sorry for my ignorance.

Thx in advance.

Jonathan Stokes SSCrazy Eights Points: 9861 More actions · Answer 5

You need to write an mdx statement to achieve this in analysis services.

Here is a link to a site that should be able to help you out. Look under the heading 'How Can I Show Percentages as Measures'. It is exactly what you want.

link = http://www.microsoft.com/resources/documentation/sql/2000/all/reskit/en-us/part6/c2361.mspx

------------------------------
The Users are always right - when I'm not wrong!