Analysis :: Calculation Of average Using DAX AVERAGE And AVERAGEX
Jun 21, 2015
Calculation of an average using DAX' AVERAGE and AVERAGEX.This is the manual calculation in DW, using SQL.In the tabular project (we're i've noticed that these 4 %'s are in itself strange), in a 1st moment i've noticed that i would have to divide by 100 to get the same values as in the DW, so i've used AVERAGEX:
The results were, respectively: 701,68; 2120,60...; -669,441; and finally **-694,74** for Avg_FMPdollar.i can't understand the difference to SQL calculation, since calculations are similar to the other ones. After that i've tried:
test:=SUM([_FMPdollar])/countrows('Fct Sales') AND the value was EQUAL to SQL: -672,17
test2:=AVERAGE('Fct Sales'[_Frontend Margin Percent ACY]), and here, without dividing by 100 in the end, -696,74...
So, AVERAGE and AVERAGEX have a diferent behaviour from the SUM divided by COUNTROWS, and even more strange, test2 doesn't need the division by 100 to be similar to AVERAGEX result.
I even calculated the number of blanks and number of zeros on each column, could it be a difference on the denominator (so, a division by a diferente number of rows), but they are equal on each row.
I have a set of data and calculated already the average of a specific measure, incl. the Standarddeviation of this measure. Now I want to create a average measure of the values on row level which arehigher than [Avg]-[StdDev] AND lower of [Avg]+[StDev].As far as I understand I have to check on Rowlevel, which means on Date and Brand Level, if the value of the measure is above or lower than the Mean +/- StdDev.
which leads to a wrong result. This one counts all rows if true.With Descendants I got a #Value-Error, probably because I did not understand how the calculations work. I planned to include this in a IIF calculation.
At the 2nd level, the calculated measure will only be correct if it is Averaged. And at the 1st level, the calculated measure will only be correct to take these Average value from 2nd level and Sum then up together.
Level 1 Level 2 A X1 X2 ================= (Avg of X1 + X2) B Y1 Y2 Y3 ================= (Avg of Y1 + Y2 + Y3) =================================================== (Sum of (Avg of X1 + X2) + (Avg of Y1 + Y2 + Y3) )
Currently, Instead of summing all the 3 averaged value, it is averaging against all the items like,
(Avg of X1 + X2 + Y1 + Y2 + Y3)
My MDX currently looks something like that
[Measure].[Value] / [Dimension].[Attribute.count] -> so i can get the avarage at the 2nd level but it doesn't require on the 1st level but retaining this value.
How can i do an average on the leaf level and using these average values to sum at parent level?
I've been asked to find the Mode of the loads on trips we run. So I run a count on the [loads] (without grouping, in an analytical function) column, then sort on the highest counts. The problem is when there is a tie. In that case, I need to find the mean of all the tied values. This means that if there is no mode at all, it would simply take the mean of all the values.
Here is sample data:
CREATE TABLE test6 ( trip char(1) NULL, date int NULL, loads int NULL
[Code] ...
And here is what I'd like to see:
CREATE TABLE test7 ( trip char(1) NULL, loads int NULL ) insert into test (trip, loads) values ('A', 15) insert into test (trip, loads) values ('B', 17) insert into test (trip, loads) values ('C', 11)
I have a temp_max column and a temp_min column with data for every day for 60 years. I want the average temp for jan of yr1 through yr60, averaged... I.E. the avg temp for Jan of yr1 is 20 and the avg temp for Jan of yr2 is 30, then the overall average is 25. The complexity lies within calculating a daily average by month, THEN a yearly average by month, in one statement. ?confused?
Here's the original query. accept platformId CHAR format a6 prompt 'Enter Platform Id (capital letters in ''): '
SELECT name, country_cd from weather_station where platformId=&&platformId;
SELECT to_char(datetime,'MM') as MO, max(temp_max) as max_T, round(avg((temp_max+temp_min)/2),2) as avg_T, min(temp_min) as min_temTp, count(unique(to_char(datetime, 'yyyy'))) as TOTAL_YEARS FROM daily WHERE platformId=&&platformId and platformId = platformId and platformId = platformId and datetime=datetime and datetime=datetime GROUP BY to_char(datetime,'MM') ORDER BY to_char(datetime,'MM');
I have created calcalated measures in a SQL Server 2012 SSAS multi dimensional model by creating empty measures in the cube and use scope statements to fill the calculation.
(so I can use measure security on calculations
as explained here )
SCOPE [Measures].[C];
THIS = IIF([B]=0,0,[Measures].[A]/[Measures].[B]);
Hello,I have two tables:[Posts] > PostId, ...[Ratings] > RatingId, PostId, RatingI want to select all posts and add a new column named AverageRating.This new column is the average of all ratings associated to that Post.If a post was not rated then its AverageRating would be NULL.How can I do this?Thanks,Miguel
I am getting the number of transactions for two different months based on dates entered from the user. I need to display the number of transactions along with the average sale. I have figured out how to get the dates and sum the transactions but I can't seem to add in the average....
I am at a loss how to calculate the group footer (Result = 30.88 ) which is equal to (96.82 + 26.70 + 0 + 0 ) / 4 . This is the result from the existing Crystal report. I am trying to convert this report to reporting services. Now I cannot nest aggregate functions and also the row number can be dynamic I am confused how to resolve it.
I was hoping someone could help me with pluging moving average into my report.
This is the template I am using for moving average: // /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] ) // This calculation returns the average value of a member over the specified time interval. FORMAT_STRING = "Standard";
This is what I have before I started to add the moving average. The report works but only displays the cummulative complexity rank.
What I want to do is also add the moving average for this over the parameter of Iterations. (IterationParam). This is what I have so far with the moving average in my report: I know I must have a lot of errors but I can't get past this first error. I highlighted the line with error. The error said: Query(7, 15) Parser: The syntax for '.' is incorrect. (msmgdsrv)
I have two tables, table a holds all the votes by users of each element in table b. I was wondering, how do I get the average of all those votes in table a that relate to that each instance of a element in table b. For example table b has two elements created by a certain user that has been voted 5 times each by users with scores like 2, 5, 4, 2 , 2 for both of them. I just need to get the average of those numbers that pertain to those elements in table b. Thanks for any help.
Hello all!This might be a newbie question, and there might be something Im just not thinking of right now, but I have a set of values that I need to get the average of but only from the middle 90%. Example:11 <-From here1234456 <- To here.7I thought I could solve it by subqueries and do the following:Select (((Select sum top 5 order asc) + (Select sum top 5 order desc)) - sum total)/rows*0.9 which would give me what I want, but I realised that when aggregating I cant order the subqueries.This is for an application (that will run the query on a sql-server) that only takes one query (although subqueries should be fine), and thats why I have a problem, I cant build any views or things like that.I guess my question is very simple: How can I get a sum of the bottom 5 percent without sorting descending?
Hi, I have two tables, Weekly and AverageEngTime. Weekly table has 14 columns, "Mon_Day,Mon_Night,Tue_day,....Sun_Night". AverageEngTime table has 15 columns, "Shifts,Mon_Day,Mon_Night,Tue_day,....Sun_Night". I have inserted "Average" as a value for column "Shifts" in AverageEngTime table. Now how do I find the average for each column in Weekly table and insert into AverageEngTime table in the respective columns, and in the row where Shifts = "Average".
I used the following codes to try but i receive errors at where statement.Please correct me. The expected output as below.
declare @Weekday tinyint, @hour int select @Weekday = datepart(dw,getdate()),@hour= datepart(hh,getdate())
if (@Weekday= 1 and (@hour>= 7 AND @hour<19)) begin Insert Into AverageEngTime(Sat_Night) where Shifts = 'Average' SELECT AVG(Sat_Night) FROM Weekly end
if ((@Weekday= 1 and @hour >= 19) OR (@Weekday = 2 and @hour < 7)) begin Insert Into AverageEngTime(Sun_Day) where Shifts = 'Average' SELECT AVG(Sun_Day) FROM Weekly end
Can anyone tell me how to calculate a moving average for the below table? -------------------- MONS /vol /counter(the date) jan/8 / 2006-01-01 feb/9 / etc. ........ oct/5 / etc. nov/6 / dec/7 / --------------------- I am using the below code, but it is having no effect...... SELECT i1.MONS, i1.COUNTER, ( SELECT AVG( i2.VOL ) FROM #RES1 AS i2 WHERE i2.MONS = i1.MONS And DATEDIFF( month , i2.COUNTER , i1.COUNTER ) Between 0 And 12 ) AS MovingAverageOver12Months FROM #RES1 AS i1 ORDER BY i1.COUNTER
I made a code to in SQL reporting services for the reports of our company regarding the new applicants. Now I need to get the average datedifference in each phase from all applicants who entered Initial/Data Collection. Example, "Initial/Data collection"PHASE will have an average of 60 minutes to be finished, averaged from all 200 applicants, and "Screening"PHASE 240 minutes to be finished, averaged from 100 applicants.
All applicants must go through stage 1 before he can go to stage 2 and so on.
The date diff will be obtained from the "createdon" column subtracted to the last "modifiedon" column in each phase.
I need to insert that code to the existing code that I have if it is possible(which my boss prefer); or either if i can manipulate it in SQL reporting services in visual studio 2005.
Here is a sample output that I have.Each phase has a different color.
Here is the code that i used SELECT subject, regardingobjectidname, createdon, modifiedon, stat, phase, initial, (CASEphaseWHEN 'Initial/Data Collection' THEN 1 WHEN 'Screening' THEN 2 WHEN 'Assesment and Selection' THEN 3 WHEN 'Placement' THEN 4 END) AS Phasesort FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, stat, initial, (CASEstatWHEN 'Application Recieved' THEN 'Initial/Data Collection' WHEN 'For Screening' THEN 'Screening' WHEN 'Shortlisted' THEN 'Screening' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'For Assesment' THEN 'Assesment and Selection' WHEN 'Passed Initial Interview' THEN 'Assesment and Selection' WHEN 'Passed Profiles Assesment' THEN 'Assesment and Selection' WHEN 'Passed Technical Exam' THEN 'Assesment and Selection' WHEN 'For Placement' THEN 'Placement' END) AS phase FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, initial, (CASEinitialWHEN '1 stage' THEN 'Application Recieved' WHEN '2 stage' THEN 'Application Recieved' WHEN '3 stage' THEN 'For Screening' WHEN '4 stage' THEN 'Shortlisted' WHEN '5 stage' THEN 'For Assesment' WHEN '6 stage' THEN 'Passed Initial Interview' WHEN '7 stage' THEN 'Passed Profiles Assesment' WHEN '8 stage' THEN 'Passed Technical Exam' WHEN '9 stage' THEN 'For Placement' WHEN '10 stage' THEN 'For Placement' END) AS stat FROM( SELECT subject, regardingobjectidname, createdon, modifiedon, (CASEsubjectWHEN N'application received' THEN '1 stage' WHEN 'process application' THEN '2 stage' WHEN 'screen application' THEN '3 stage' WHEN 'Phone interview' THEN '4 stage' WHEN N'initial interview' THEN '5 stage' WHEN 'profiles assessment' THEN '6 stage' WHEN 'technical exam and interview' THEN '7 stage' WHEN 'background and reference check' THEN '8 stage' WHEN 'Job Offer' THEN '9 stage' WHEN 'Contract Signing' THEN '10 stage' END) AS initial FROM(SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredTask WHERE (subject IN ('application received', 'process application', 'screen application', 'initial interview', 'profiles assessment', 'technical exam and interview', 'background and reference check', 'job offer', 'contract signing')) UNION ALL SELECT subject, regardingobjectidname, createdon, modifiedon FROMFilteredPhoneCall WHEREsubject = 'phone interview' ) AS Orion ) AS Initials ) AS Phases ) AS Stats ORDER BY regardingobjectidname, initial
I appreciate your help.
Thanks a lot.
__________________________________________________ Your future is made by the things you are presently doing.
SELECT dbo.TechphonesCalibQA.ID, dbo.TechphonesCalibQA.EmpID, dbo.TechphonesCalibQA.EmpName, dbo.TechphonesCalibQA.[Level], CASE dbo.TechphonesCalibQA.G_Branded WHEN dbo.TechphonesCalibClient.G_Branded THEN 1 ELSE 0 END AS G_Branded FROM dbo.TechPhonesCalibClient INNER JOIN dbo.TechphonesCalibQA ON dbo.TechPhonesCalibClient.SesID = dbo.TechphonesCalibQA.SesID
I would like to get the average of column G_branded (Result from the above formula) Any idea?
Apologies for the simplicity of the question, but it reflects mycapabilities! I have the following sample fields coming from differenttables:LocationTimeDate (timestamp)DataI need to return the average of Data per Location per HOUR.Thanks.
Hi AllWe have an order processing database which includes the standard OrderHeader/Order Lines tables. I'm trying to write a query to get the averagenumber of lines per order over a given period, and I'm stuck :-(I can get the number of lines per order with:SELECT COUNT(*) FROM OrderDetailsINNER JOIN Order Header ONOrderHeader.OrderNo = OrderDetails.OrderNoWHERE OrderHeader.OrderDate ..... {various criteria} ...GROUP BY OrderDetails.OrderNumberBut how do I then get an average of this for the period?TIAMike Bannon
I created a query that could give me the % of each product that are ordered alone. I tried modifying it to give me avg check but ran into problems because the when i use max(product) to aggergate the field, it uses the products with the highest first character in the alphabet thus it products with V's and Y's get a lot more results (
Is there an easy way to do this by creating a temp table or nested query?
I can create a nested query to give me the average check for only one product at a time. I'm looking for something that can group the average check by all product.
time_stamp met_id ch1avg 12/7/2006 12:00:00 AM 0970 .5 12/7/2006 12:10:00 AM 0970 1.10
I have a table which collects data at 10 minute intervals, so per day, per met_id there are 144 records. Averaging ch1avg based on a single date query correctly produces 144 records. I am trying produce 144 record average based on any date spread in the where clasue. For example if the where is time_stamp between '01/01/2007' and '01/03/2007' this would be 288 records however the ouput needs to always be grouped into 144 records maximum. Looking for any assistance.
Hi All, I am trying to create a report that shows moving average. Well I found this template:
// /*Returns the average value of a member over a specified time interval.*/ CREATE MEMBER CURRENTCUBE.[MEASURES].[Moving Average] AS Avg ( [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember.Lag(<<Periods to Lag>>) : [<<Target Dimension>>].[<<Target Hierarchy>>].CurrentMember, [Measures].[<<Target Measure>>] )
// This calculation returns the average value of a member over the specified time interval. , FORMAT_STRING = "Standard";
Only I do not have a specified interal or a constant number that I can place into
.Lag(<<Periods to Lag>>) The report I have has a parameter that allows you to select a specific team project from a list. Although I have not figured out how to get the iteration paramter to update to only show the specified team project iterations, I want the graph to only show the iterations for that team project and the moving average (in the graph). The problem is that I can't just plug in the number 6 because different projects we have, have different numbers of iterations, and the user can pick any project from the list in the parameter.
Just want to gathr some ideas.I have a matrix where i retrieve Fields!Amount.I used the subtotal property to get the total amount per quarter.But how can i get the average of the 3 months.I tried using the Avg(Fields!Amount.Value) but it's results is unexpected or wrong. ________________________ | Avg(Fields!Amount.value) | | Jan | Feb | March | ------------------------------------------- | 0.1 | 0.2 | 0.2 | | 0.3 | 0.1 | 0.5 | SubTotal | 0.4 | 0.3 | 0.7 |
That is the design of my matrix.And other thing,it is dynamic,for example,There is a possibility that Feb could be absent.SO i cannot make Sum(Fields!Amount.Value) / 3. Dont know what to do for that.Why could Avg be wrong, correct me if im wrong,but as for what i know,i think the formula for Avg in my matrix is :
hi guys I have a list of data, but I want to calculate the average. for example
dh from depth bi a 0 1 4.5 a 1 2 3.2
a 2 5 8.2 a 5 7 10.5
a 7 10 5 a 10 15 7.8
a 15 20 9.5 a 20 22 10.2 b 0 5 2.3 b 5 7 3.1 b 7 9 4.6 b 9 12.5 12 b 12.5 15 5.5 b 15 17 8.9 b 17 19 10
this means that for each interval I have a value in the field bi and I want to calculate an average for the total for each dh something like this for a group of values dh bi a ( (depth-from)* bi)/sum(depth-from)
I don't even know if this is possible but here is a picture of some sample results.
Description Amount Account Location Begin Balance
Test 1234 $100 0001 Las Vegas $5000 Test 2345 $50 0001 Las Vegas $5000 Test 3456 $80 0001 Phoenix $2000
What this is, is transactions and their amounts and account/location they went to. The last column is the beginning balance for the account/location combination. I only need the beginning balance once per account/location or averaged. So I need either this (beginning balance only shows once).....
Description Amount Account Location Begin Balance
Test 1234 $100 0001 Las Vegas $5000 Test 2345 $50 0001 Las Vegas $0 Test 3456 $80 0001 Phoenix $2000
or I need this (beginning balance is averaged)......
Description Amount Account Location Begin Balance
Test 1234 $100 0001 Las Vegas $2500 Test 2345 $50 0001 Las Vegas $2500 Test 3456 $80 0001 Phoenix $2000
That way I can do a sum on the beginning balance results in my report for the account/location combination, and do a sum on just account or just location while still having my transactions.
