asked on 10/10/2011

SSAS Bids Measure

Hi all i am currently building a cube in BIDS i have created a measure the measure represents the a column called Pounds/sterling for the cost of an order.

I am creating a dashboard that will show the average order value based on months i have created my Time dimension etc already all that is working fine, but cannot get the correct results when selecting the AVERAGE aggregation on the measure.

Not sure if i am doing something wrong here but when i add the measure i select the data type as Currency and the Aggregation as Average of Children.

i will give you an example of what i would expect to see for the month of September i have a query in SQL that shows All the orders for september and my measure is based on a column in that query called pound_sterling,

so i am creating the measure correctly if i want the average of all orders placed in september because the value i get is not correct

John

Microsoft SQL Server 2008 SSRS SSAS

Last Comment

Tim Humphries

10/11/2011

Tim Humphries

10/11/2011

Hi, I think you probably want SUM as the aggregation type and to define a calculated member that will deteremine the average over the hierachies you will be analysing by.
Will you be interested in the average based on anything other than time?

An example would be useful, together with the names of you dimensions / hierarchies.

Tim

p.s. this question would be better in OLAP zone.

pepps11976

10/11/2011

ASKER

Hi TimHumphries

for the time being i will just be anaylysing over time i might add to it later, so if i was to aggregate by sum how do i go about creating the calculated member, my time dimension is just called time, and my measure is called poundsterling

john

Tim Humphries

10/11/2011

Hi,

You'd need to define a calculated measure that determines the average based on the selected time periods and orders.
Does your measure group also include a Count measure?

Could you post your example? - it will make it easier.

Thanks

Tim

pepps11976

10/11/2011

ASKER

Hi Tim

sorry for the newbie questions but what exactly do you need to see i am not sure how to show the example within BIDS do you mean to see screen shots etc?

john

Tim Humphries

10/11/2011

Well, you said you could give an example of what you expected to see. Also, you could probably get a screenshot of what you are currently seeing, highlighting the values that are wrong. This can also help in seeing the rows / columns by which you are viewing your data.
Finally, the actual names of measures and dimensions and hierachies will help in getting the calculated measure syntax right.

Tim

pepps11976

10/11/2011

ASKER

Ok This is is the view from which i would like to calculate averages of orders based on My Time Dimension

SELECT     TOP (100) PERCENT (CASE WHEN dbo.itran.it_fcrate = 0 THEN (dbo.itran.it_quan * dbo.itran.it_price / 100) 
                      ELSE (dbo.itran.it_quan * dbo.itran.it_price / dbo.itran.it_fcrate / 100) END) AS it_total_gbp, dbo.ihead.ih_account, dbo.ihead.ih_name, dbo.itran.it_doc, 
                      dbo.ihead.ih_orddate, dbo.ihead.ih_sorder, dbo.ihead.ih_terrtry, dbo.slook.lk_desc, dbo.ssale.ss_acode, LEFT(dbo.itran.it_anal, 3) AS group_anal, dbo.itran.it_anal, 
                      dbo.ssale.ss_adesc, dbo.sname.sn_account, dbo.ihead.ih_doc AS iheaddocnumber
FROM         dbo.ihead INNER JOIN
                      dbo.slook ON dbo.ihead.ih_terrtry = dbo.slook.lk_code LEFT OUTER JOIN
                      dbo.sname ON dbo.ihead.ih_account = dbo.sname.sn_account LEFT OUTER JOIN
                      dbo.ssale RIGHT OUTER JOIN
                      dbo.itran ON dbo.ssale.ss_acode = dbo.itran.it_anal ON dbo.ihead.ih_doc = dbo.itran.it_doc
WHERE     (dbo.itran.it_status = 'A') AND (dbo.itran.it_anal <= 'ZZZ') AND (dbo.itran.it_doc <= 'WOR') AND (dbo.ihead.ih_sorder <> ' ') AND (dbo.ssale.ss_acode IS NOT NULL)
ORDER BY dbo.ihead.ih_account

Open in new window

These are my current measures I have one called Total Order Value at the moment this gives me the total Order Value Over Time

I did start to create this but was using the Aggregation of "average Of Children" in the drop down of the measure box but realised that this was the wrong way to do it

here is a screen shot of my dimensions

.

I see what you are saying about creating a calculated measure but i have never created one before.

Thanks for your help

John

ASKER CERTIFIED SOLUTION

Tim Humphries

10/11/2011

THIS SOLUTION IS ONLY AVAILABLE TO MEMBERS.

View this solution by signing up for a free trial.

Members can start a 7-Day free trial and enjoy unlimited access to the platform.

See Pricing Options

Start Free Trial