MDX formula for calculating All-Commodity Volume (%ACV)

0
votes

I would like to create a measure for the All-Commodity Volume (ACV) indicator. The formula is the following :

Total Sales of Resellers Who Sold a Product / Total Sales of All Resellers

Let's take an example, for instance the product "LL Touring Frame - Blue, 44" from AdventureWorks2012DW Cube.

We can see it is sold by 14 resellers

enter image description here

These 14 resellers together make 2 706 909 $ of the sales

enter image description here

If I take all the resellers, they make 80 450 596 $ of the sales

enter image description here

My ACV measure would return 2 607 909 / 80 450 596 = 2% for the product "LL Touring Frame - Blue, 44", which means it present at the resellers who make 2% of my indirect sales.

What I'm looking for is the MDX expression which takes the "Reseller Sales Amount for all products" instead of the reseller sales amount for the selected product, but only if the "Reseller Sales Amount of the selected product is not null".

enter image description here

So far, thanks the help of the community I could make a query which gives me the correct result if I filter on some specific products :

WITH SET ActiveStores As NonEmpty( [DimStore].[Store].[Store] , { ([DimProduct].[Product].CurrentMember, [Measures].[Sales])} )

MEMBER ACV AS SUM(ActiveStores, [Measures].[Store Sales]) / [Measures].[Store Sales]

SELECT NON EMPTY ACV ON COLUMNS, NON EMPTY { ([DimProduct].[Product].[Product].ALLMEMBERS ) } ON ROWS FROM ( SELECT ( { [DimProduct].[Product].&[ HAMAC DE VOYAGE] } ) ON COLUMNS FROM [Cube])

This query returns an ACV of 9% for the filtered product. However, if I remove the filter by putting only FROM [Cube], the ACV becomes 100% for all the products, even the one which was 9% with the previous query.

It is like the ActiveStores set is binded with the WHERE clause but not the ROWS Axis.

Regards

3
ssasmdx
I'm not understanding something. Your MDX looks good. Can't you just replace [Measures].[Distinct Number of Stores] with [Measures].[Sales] and be done? – GregGalloway
GregGalloway, if I do that I get the amount of sales of the selected product in the stores where it is sold, what I need is the sales of all product in the stores where the selected product is sold. I have updated my post by adding more details. – user2018454

3 Answers

0
votes

To find members where Sales is not null you can use the NonEmpty function:

NonEmpty(
  [Stores].[Stores].members
  ,(
     [Product].[Product].[ProductA]
   , [Measures].[Sales]
   )
)
0
votes

What about:

Sum(
 [Store].[Store].[Store].Members,
 IIF(
  IsEmpty([Measures].[Sales]),
  Null,
  (
    ROOT([Product])
   ,ROOT([Supplier])
   ,ROOT([Promotion])
   ,ROOT([Misc])
   ,[Measures].[Sales]
  )
 )
)
0
votes

I could find a solution this way :

I have added an aggregated fact (Store Sales) as an attribute of dimension Store. Created a Measure Store Sales out of it, then created a calculated measure with the following expression :

SUM(NonEmpty(
            [DimStore].[Store].[Store]
            ,([Measures].[Sales])
        ), [Measures].[Store Sales]) / (ROOT([DimStore]), [Measures].[Store Sales])