EducationSoftwareStrategy.com
StrategyCommunity

Knowledge Base

Product

Community

Knowledge Base

TopicsBrowse ArticlesDeveloper Zone

Product

Download SoftwareProduct DocumentationSecurity Hub

Education

Tutorial VideosSolution GalleryEducation courses

Community

GuidelinesGrandmastersEvents
x_social-icon_white.svglinkedin_social-icon_white.svg
Strategy logoCommunity

© Strategy Inc. All Rights Reserved.

LegalTerms of UsePrivacy Policy
  1. Home
  3. Topics

KB7963: How to create an Average metric from an attribute description that joins to the fact table to calculate a weighted average in MicroStrategy

Stefan Zepeda

Salesforce Solutions Architect • Strategy

This article notes how to create a weighted average metric

In Strategy, users can create an Average metric from an attribute description that will join to the fact table to calculate a weighted average.
 
The example shown in this document references the following three sample tables:
 
LU_RATE
 

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

 
LU_MONTH
 

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

 
FACT_1
 

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

DB object: “Microsoft SQL Server 2012”
RATE_IDRATE_DESC118.9212.635.749.7MONTH_IDMONTH_DESC200201Jan 02200202Feb 02200203Mar 03RATE_IDMONTH_IDFACT112002012120020342200201622002028220020310320020212420020314

 
In a situation such as this, the Average RATE_DESC can be calculated in two ways:

  1. A non-weighted average of all of the possible rates. The SQL for this looks like the following:
    select avg(rate_desc)
    from lu_rate
    Using the examples in the tables above, this SQL results in an Average rate of 11.725.
     
  2. A weighted average that takes into account how many times this rate is used in the fact table. The SQL for this looks like the following:
    select AVG(a12.RATE_DESC)
    from FACT_1 a11
    join LU_RATE a12
    on (a11.RATE_ID = a12.RATE_ID)
    Using the examples in the tables above, this SQL results in an Average rate of 13.

 
By default in Strategy, if users create a metric with the definition Avg(Rate@desc), the SQL generated is similar to Number 1 above.
 
To generate the SQL shown in Number 2 above, users must enter the metric definition as shown below:
Avg<FactID=Fact1>(Rate@DESC) {~+}
 
The <FACTID=FACT1> parameter forces the join to the FACT_1 fact table.
 
 

Comment

0 comments

Details

Knowledge Article

Published:

May 18, 2017

Last Updated:

May 18, 2017