[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


SSAS Bids Measure

Posted on 2011-10-10
Medium Priority
Last Modified: 2016-02-14
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

Question by:pepps11976
  • 4
  • 3
LVL 15

Expert Comment

by:Tim Humphries
ID: 36947271
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.


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

Author Comment

ID: 36947444
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

LVL 15

Expert Comment

by:Tim Humphries
ID: 36947549

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.



Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.


Author Comment

ID: 36947596
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?

LVL 15

Expert Comment

by:Tim Humphries
ID: 36947681
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.


Author Comment

ID: 36947714
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

LVL 15

Accepted Solution

Tim Humphries earned 2000 total points
ID: 36947888

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

CREATE MEMBER CURRENTCUBE.[Measures].[Average Order Value]

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

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

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.


Featured Post

Configuration Guide and Best Practices

Read the guide to learn how to orchestrate Data ONTAP, create application-consistent backups and enable fast recovery from NetApp storage snapshots. Version 9.5 also contains performance and scalability enhancements to meet the needs of the largest enterprise environments.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Is your OST file inaccessible, Need to transfer OST file from one computer to another? Want to convert OST file to PST? If the answer to any of the above question is yes, then look no further. With the help of Stellar OST to PST Converter, you can e…

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question