{"id":8580,"date":"2022-07-05T11:31:00","date_gmt":"2022-07-05T09:31:00","guid":{"rendered":"https:\/\/expert-only.com\/?p=8580"},"modified":"2022-07-07T12:50:39","modified_gmt":"2022-07-07T10:50:39","slug":"median-value-of-ssas-members-in-mdx","status":"publish","type":"post","link":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/","title":{"rendered":"Median value of SSAS members in MDX"},"content":{"rendered":"\n<p>How to calculate the median value of SSAS members in MDX in a Microsoft SSAS cube? The median function, with the MEDIAN syntax, is the only SSAS function to use. The AVG function, for example, is used to calculate member averages. Rather than inventing a calculated member that will count the distinct number of members in order to average them.<\/p>\n\n\n\n<p>Indeed, with the MDX language, to avoid complicated and useless calculations, use existing functions. It is important to be well informed about all the possibilities offered by the function set.<\/p>\n\n\n\n<p>Training or following SSAS tutorials is therefore essential.<\/p>\n\n\n\n<h2 class=\"wp-block-heading\" id=\"h-calculating-the-median-value-of-mdx-members-in-an-ssas-cube\">Calculating the median value of MDX members in an SSAS cube<\/h2>\n\n\n\n<p>Here is an example of an <strong><a href=\"https:\/\/expert-only.com\/en\/it-concepts\/mdx-stands-for-multidimensional-expressions\/\">MDX<\/a><\/strong> script for use with Analysis Services. This example creates a calculated indicator based on an existing sales indicator. The formula uses the Date dimension.<\/p>\n\n\n\n<p>It is used on an SSAS cube that calculates the median of an indicator for a dataset:<\/p>\n\n\n\n<pre class=\"EnlighterJSRAW\" data-enlighter-language=\"mssql\" data-enlighter-theme=\"\" data-enlighter-highlight=\"\" data-enlighter-linenumbers=\"\" data-enlighter-lineoffset=\"\" data-enlighter-title=\"\" data-enlighter-group=\"\">WITH MEMBER [Measures].[INDICATOR]\nAS MEDIAN(\n[Date].[Calendar].CurrentMember.Children,\n[Measures].[ProductSales].\n)\nSELECT [Measures].[INDICATOR] ON 0,\nNO EMPTY [Date].[Calendar].[Month] * \n[Products].[Product Categories].[Sub-Categories].MEMBERS * \n[Geography].[Geography].[Country].MEMBERS ON 1\nFROM [YOUR_CUBE_SSAS]<\/pre>\n\n\n\n<p>Note that the <a href=\"https:\/\/en.wikipedia.org\/wiki\/Median\" target=\"_blank\" rel=\"noreferrer noopener\">median<\/a> is done by displaying the axes of months, product sub-categories and also countries.<\/p>\n\n\n\n<h3 class=\"wp-block-heading\">Definition of a median value<\/h3>\n\n\n\n<p>In mathematics, and more precisely in statistics, a median is the value that separates two sets of data. Also called a group, it is the lower and upper halves of a data set.<\/p>\n\n\n\n<p>That is, there are as many values below the median value as there are above. This is quite different from an average because it is the number of members that is considered here, not the value of the members.<\/p>\n\n\n\n<p>This short article on Microsoft OLAP technologies shows an example of an MDX script to calculate the median value of members calculated with SSAS.<\/p>\n\n\n\n<p>Finally, here is a <strong><a href=\"https:\/\/expert-only.com\/en\/power-bi\/year-to-date-value-with-power-bi-and-dax\/\">DAX formula to calculate the Year-To-Date value of a measure<\/a><\/strong> as the sales of the past year for example.<\/p>\n\n\n\n<figure class=\"wp-block-embed is-type-wp-embed is-provider-tutoriels-et-exemples-sql-server-et-microsoft-it wp-block-embed-tutoriels-et-exemples-sql-server-et-microsoft-it\"><div class=\"wp-block-embed__wrapper\">\nhttps:\/\/expert-only.com\/en\/power-bi\/year-to-date-value-with-power-bi-and-dax\/\n<\/div><\/figure>\n","protected":false},"excerpt":{"rendered":"<div class=\"mh-excerpt\"><p>How to calculate the median value of SSAS members in MDX in a Microsoft SSAS cube? The median function, with the MEDIAN syntax, is the only SSAS function to use. The AVG function, for example, is used to calculate <a class=\"mh-excerpt-more\" href=\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\" title=\"Median value of SSAS members in MDX\">&#8230;<\/a><\/p>\n<\/div>","protected":false},"author":1,"featured_media":7852,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[600],"tags":[],"class_list":{"0":"post-8580","1":"post","2":"type-post","3":"status-publish","4":"format-standard","5":"has-post-thumbnail","7":"category-ssas"},"yoast_head":"<!-- This site is optimized with the Yoast SEO Premium plugin v21.7 (Yoast SEO v26.2) - https:\/\/yoast.com\/wordpress\/plugins\/seo\/ -->\n<title>Median value of SSAS members in MDX - Analysis Services<\/title>\n<meta name=\"description\" content=\"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.\" \/>\n<meta name=\"robots\" content=\"index, follow, max-snippet:-1, max-image-preview:large, max-video-preview:-1\" \/>\n<link rel=\"canonical\" href=\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\" \/>\n<meta property=\"og:locale\" content=\"en_US\" \/>\n<meta property=\"og:type\" content=\"article\" \/>\n<meta property=\"og:title\" content=\"Median value of SSAS members in MDX\" \/>\n<meta property=\"og:description\" content=\"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.\" \/>\n<meta property=\"og:url\" content=\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\" \/>\n<meta property=\"og:site_name\" content=\"SQL and IT Tutorials\" \/>\n<meta property=\"article:publisher\" content=\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\" \/>\n<meta property=\"article:published_time\" content=\"2022-07-05T09:31:00+00:00\" \/>\n<meta property=\"article:modified_time\" content=\"2022-07-07T10:50:39+00:00\" \/>\n<meta property=\"og:image\" content=\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg\" \/>\n\t<meta property=\"og:image:width\" content=\"1920\" \/>\n\t<meta property=\"og:image:height\" content=\"1080\" \/>\n\t<meta property=\"og:image:type\" content=\"image\/jpeg\" \/>\n<meta name=\"author\" content=\"Expert-Only\" \/>\n<meta name=\"twitter:card\" content=\"summary_large_image\" \/>\n<meta name=\"twitter:creator\" content=\"@expert_only\" \/>\n<meta name=\"twitter:site\" content=\"@expert_only\" \/>\n<meta name=\"twitter:label1\" content=\"Written by\" \/>\n\t<meta name=\"twitter:data1\" content=\"Expert-Only\" \/>\n\t<meta name=\"twitter:label2\" content=\"Est. reading time\" \/>\n\t<meta name=\"twitter:data2\" content=\"2 minutes\" \/>\n<script type=\"application\/ld+json\" class=\"yoast-schema-graph\">{\"@context\":\"https:\/\/schema.org\",\"@graph\":[{\"@type\":\"Article\",\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#article\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\"},\"author\":{\"name\":\"Expert-Only\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\"},\"headline\":\"Median value of SSAS members in MDX\",\"datePublished\":\"2022-07-05T09:31:00+00:00\",\"dateModified\":\"2022-07-07T10:50:39+00:00\",\"mainEntityOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\"},\"wordCount\":311,\"commentCount\":0,\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg\",\"articleSection\":[\"SSAS\"],\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"CommentAction\",\"name\":\"Comment\",\"target\":[\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#respond\"]}]},{\"@type\":\"WebPage\",\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\",\"url\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\",\"name\":\"Median value of SSAS members in MDX - Analysis Services\",\"isPartOf\":{\"@id\":\"https:\/\/expert-only.com\/en\/#website\"},\"primaryImageOfPage\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage\"},\"thumbnailUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg\",\"datePublished\":\"2022-07-05T09:31:00+00:00\",\"dateModified\":\"2022-07-07T10:50:39+00:00\",\"description\":\"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.\",\"breadcrumb\":{\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#breadcrumb\"},\"inLanguage\":\"en-US\",\"potentialAction\":[{\"@type\":\"ReadAction\",\"target\":[\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/\"]}]},{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg\",\"width\":1920,\"height\":1080},{\"@type\":\"BreadcrumbList\",\"@id\":\"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#breadcrumb\",\"itemListElement\":[{\"@type\":\"ListItem\",\"position\":1,\"name\":\"en\",\"item\":\"https:\/\/expert-only.com\/en\/\"},{\"@type\":\"ListItem\",\"position\":2,\"name\":\"Median value of SSAS members in MDX\"}]},{\"@type\":\"WebSite\",\"@id\":\"https:\/\/expert-only.com\/en\/#website\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"name\":\"SQL and IT Tutorials\",\"description\":\"\",\"publisher\":{\"@id\":\"https:\/\/expert-only.com\/en\/#organization\"},\"potentialAction\":[{\"@type\":\"SearchAction\",\"target\":{\"@type\":\"EntryPoint\",\"urlTemplate\":\"https:\/\/expert-only.com\/en\/?s={search_term_string}\"},\"query-input\":{\"@type\":\"PropertyValueSpecification\",\"valueRequired\":true,\"valueName\":\"search_term_string\"}}],\"inLanguage\":\"en-US\"},{\"@type\":\"Organization\",\"@id\":\"https:\/\/expert-only.com\/en\/#organization\",\"name\":\"Expert-Only\",\"url\":\"https:\/\/expert-only.com\/en\/\",\"logo\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\",\"url\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"contentUrl\":\"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg\",\"width\":381,\"height\":174,\"caption\":\"Expert-Only\"},\"image\":{\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/\"},\"sameAs\":[\"https:\/\/www.facebook.com\/ExpertOnlyCom\/\",\"https:\/\/x.com\/expert_only\",\"https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA\"]},{\"@type\":\"Person\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef\",\"name\":\"Expert-Only\",\"image\":{\"@type\":\"ImageObject\",\"inLanguage\":\"en-US\",\"@id\":\"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/\",\"url\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"contentUrl\":\"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g\",\"caption\":\"Expert-Only\"}}]}<\/script>\n<!-- \/ Yoast SEO Premium plugin. -->","yoast_head_json":{"title":"Median value of SSAS members in MDX - Analysis Services","description":"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.","robots":{"index":"index","follow":"follow","max-snippet":"max-snippet:-1","max-image-preview":"max-image-preview:large","max-video-preview":"max-video-preview:-1"},"canonical":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/","og_locale":"en_US","og_type":"article","og_title":"Median value of SSAS members in MDX","og_description":"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.","og_url":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/","og_site_name":"SQL and IT Tutorials","article_publisher":"https:\/\/www.facebook.com\/ExpertOnlyCom\/","article_published_time":"2022-07-05T09:31:00+00:00","article_modified_time":"2022-07-07T10:50:39+00:00","og_image":[{"width":1920,"height":1080,"url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg","type":"image\/jpeg"}],"author":"Expert-Only","twitter_card":"summary_large_image","twitter_creator":"@expert_only","twitter_site":"@expert_only","twitter_misc":{"Written by":"Expert-Only","Est. reading time":"2 minutes"},"schema":{"@context":"https:\/\/schema.org","@graph":[{"@type":"Article","@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#article","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/"},"author":{"name":"Expert-Only","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef"},"headline":"Median value of SSAS members in MDX","datePublished":"2022-07-05T09:31:00+00:00","dateModified":"2022-07-07T10:50:39+00:00","mainEntityOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/"},"wordCount":311,"commentCount":0,"publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg","articleSection":["SSAS"],"inLanguage":"en-US","potentialAction":[{"@type":"CommentAction","name":"Comment","target":["https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#respond"]}]},{"@type":"WebPage","@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/","url":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/","name":"Median value of SSAS members in MDX - Analysis Services","isPartOf":{"@id":"https:\/\/expert-only.com\/en\/#website"},"primaryImageOfPage":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage"},"image":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage"},"thumbnailUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg","datePublished":"2022-07-05T09:31:00+00:00","dateModified":"2022-07-07T10:50:39+00:00","description":"In an OLAP cube, to calculate the median value of SSAS members with an MDX script, use this formula with the MEDIAN function.","breadcrumb":{"@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#breadcrumb"},"inLanguage":"en-US","potentialAction":[{"@type":"ReadAction","target":["https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/"]}]},{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#primaryimage","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2022\/05\/black-white-city-street-026A76F002A_1920x1080.jpeg","width":1920,"height":1080},{"@type":"BreadcrumbList","@id":"https:\/\/expert-only.com\/en\/ssas\/median-value-of-ssas-members-in-mdx\/#breadcrumb","itemListElement":[{"@type":"ListItem","position":1,"name":"en","item":"https:\/\/expert-only.com\/en\/"},{"@type":"ListItem","position":2,"name":"Median value of SSAS members in MDX"}]},{"@type":"WebSite","@id":"https:\/\/expert-only.com\/en\/#website","url":"https:\/\/expert-only.com\/en\/","name":"SQL and IT Tutorials","description":"","publisher":{"@id":"https:\/\/expert-only.com\/en\/#organization"},"potentialAction":[{"@type":"SearchAction","target":{"@type":"EntryPoint","urlTemplate":"https:\/\/expert-only.com\/en\/?s={search_term_string}"},"query-input":{"@type":"PropertyValueSpecification","valueRequired":true,"valueName":"search_term_string"}}],"inLanguage":"en-US"},{"@type":"Organization","@id":"https:\/\/expert-only.com\/en\/#organization","name":"Expert-Only","url":"https:\/\/expert-only.com\/en\/","logo":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/","url":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","contentUrl":"https:\/\/expert-only.com\/wp-content\/uploads\/2017\/09\/cropped-logo_Expert-Only.jpg","width":381,"height":174,"caption":"Expert-Only"},"image":{"@id":"https:\/\/expert-only.com\/en\/#\/schema\/logo\/image\/"},"sameAs":["https:\/\/www.facebook.com\/ExpertOnlyCom\/","https:\/\/x.com\/expert_only","https:\/\/www.youtube.com\/channel\/UCMS5sR_FwAetB0FmciNvUaA"]},{"@type":"Person","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/406a9576b52944f018739a42046873ef","name":"Expert-Only","image":{"@type":"ImageObject","inLanguage":"en-US","@id":"https:\/\/expert-only.com\/en\/#\/schema\/person\/image\/","url":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","contentUrl":"https:\/\/secure.gravatar.com\/avatar\/084b15660763ff5b13bb60b2f52f97bb?s=96&d=identicon&r=g","caption":"Expert-Only"}}]}},"_links":{"self":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8580","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/comments?post=8580"}],"version-history":[{"count":0,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/posts\/8580\/revisions"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media\/7852"}],"wp:attachment":[{"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/media?parent=8580"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/categories?post=8580"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/expert-only.com\/en\/wp-json\/wp\/v2\/tags?post=8580"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}