Azure OLAP cube giving incorrect totals

Question

We use Azure Analysis Services to host our cubes. We encountered a problem while reading data through EXCEL.

The problem is that the totals (and subtotals) are being calculated incorrectly. Initially, we found this issue in EXCEL. The pivot table grand total row was displaying the incorrect amount which is supposed to be the sum al all rows. Latter found out we can reproduce the de issue in an MDX query also.

The column values are given by a calculated measure which calculates the sold items (volume) of a product a year back in time.

E.g. For product A, we look a year back in time and make a sum of all the items sold. This works great if we have a filter for a specific year. It fails is when we need a subtotal or a total without any year filters applied or when we have multiple years selected in the filter.

Here's the DAX formula used to calculate the volume:

IF ( 
    HASONEVALUE( 'Calendar'[Year] ),
    CALCULATE( 
        [volume],
        FILTER( 
            ALL( Calendar ),
            'Calendar'[Year] = VALUES( 'Calendar'[Year] )-1
            && 'Calendar'[ISO_DAY] <= MAX( 'Calendar'[ISO_DAY] )
        )
    ),
    IF(ISBLANK([Turnover]),BLANK(),CALCULATE([Volume],SAMEPERIODLASTYEAR(DATESBETWEEN('Calendar'[PK_Date],[MinSalesDateThisYear],[MaxSalesDate]))))
)

Turnover - sum and filter by stores
Volume - sum and filter by stores
MinSalesDateThisYear - the first day of the current year
MaxSalesDate - the last day we sold a product

Stated SSMS profiler and captured what is EXCEL generating.

Without years filter (incorrect totals):

SELECT 
  { 
    [Measures].[Volume LYTD] 
  } DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON COLUMNS, 
  NON EMPTY HIERARCHIZE( 
    ADDCALCULATEDMEMBERS( 
      { 
        DRILLDOWNLEVEL({ 
          [Products].[Product].[All] 
        }) 
      } 
    ) 
  ) DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON ROWS 
FROM (SELECT 
       ( 
         { 
           [Products].[Product].&[1],
           [Products].[Product].&[2],
           [Products].[Product].&[3],
           [Products].[Product].&[4],
           [Products].[Product].&[5],
            ...
         } 
       ) ON COLUMNS 
     FROM [MyCube]) 
WHERE ( 
        [Calendar].[YW].[All] 
      ) CELL PROPERTIES value, format_string, language, back_color, fore_color, 
font_flags

-----       Volume LYTD
All         126715
Product1    26337
Product2    14525
Product3    9602
Product4    42492
Product5    28494
Product6    5154

With year filter (correct totals):

SELECT 
  NON EMPTY HIERARCHIZE( 
    ADDCALCULATEDMEMBERS( 
      { 
        DRILLDOWNLEVEL({ 
          [Products].[Product].[All] 
        }) 
      } 
    ) 
  ) DIMENSION PROPERTIES parent_unique_name, hierarchy_unique_name ON COLUMNS 
FROM (SELECT 
       ( 
         { 
           [Products].[Product].&[1],
           [Products].[Product].&[2],
           [Products].[Product].&[3],
           [Products].[Product].&[4],
           [Products].[Product].&[5],
            ...
         } 
       ) ON COLUMNS 
     FROM [MyCube]) 
WHERE ( 
        [Calendar].[YW].[Year].&[2018], 
        [Measures].[Volume LYTD] 
      ) CELL PROPERTIES value, format_string, language, back_color, fore_color, 
font_flags

All     roduct1 Product2    Product3    Product4    Product5    Product6    Product7    Product8    Product9    Product10
150060  3352    11545       24037       4           11848       4           4           55088       35049       9129

I cannot seem to grasp what is happening here. I also found an article MS pointed that EXCEL is calculating totals incorrect due to the order of the operation he does, but since I can reproduce this at an MDX query I suspect is something the DAX functions or the cube.

Does anyone have any ideas of what I can do next?

Also, I know I might not have provided some information, so please ask. I'll be happy to provide them.

Note: I know there is a post with a similar issue, but I think this problem is a bit different and plus I didn't want to hijack that post.

GregGalloway GregGalloway · Accepted Answer · 2019-10-24T12:22:31

Try calculating by year then rolling up:

SUMX( 
    VALUES( 'Calendar'[Year] ),
    CALCULATE( 
        [volume],
        FILTER( 
            ALL( Calendar ),
            'Calendar'[Year] = VALUES( 'Calendar'[Year] )-1
            && 'Calendar'[ISO_DAY] <= MAX( 'Calendar'[ISO_DAY] )
        )
    )
)

Azure OLAP cube giving incorrect totals

1 Answers