• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 582
  • Last Modified:

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
0
pepps11976
Asked:
pepps11976
  • 4
  • 3
1 Solution
 
Tim HumphriesDirectorCommented:
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.
0
 
pepps11976Author Commented:
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
0
 
Tim HumphriesDirectorCommented:
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

0
Cloud Class® Course: Python 3 Fundamentals

This course will teach participants about installing and configuring Python, syntax, importing, statements, types, strings, booleans, files, lists, tuples, comprehensions, functions, and classes.

 
pepps11976Author Commented:
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
0
 
Tim HumphriesDirectorCommented:
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
0
 
pepps11976Author Commented:
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  ScreenShot of Measures
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 Screen Shot of dimensions.

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

Thanks for your help

John
0
 
Tim HumphriesDirectorCommented:
Ok,

I think you should probably be able to use the following:

CREATE MEMBER CURRENTCUBE.[Measures].[Average Order Value]
AS  NULL;  

SCOPE ([Measures].[Average Order Value]);  

        THIS = (SUM([Measures].[Order Value]) / SUM([Measures].[Total Orders]));  
         
END SCOPE;

You enter this in the Calculations tab for your cube.

Once defined and 'Saved' to the cube, test it by checking the average calcuation at different levels in your time hierarchy to make sue it's ok.

Tim
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now