Calculation group does not properly aggregate measures created using DIVIDE()

[tenbrooks10]

Calculation groups in Analysis Services (AS) are incredibly powerful for doing quick rolling 12 month (R12) type aggregations to smooth trend data (among many other cool things that calculation groups do). After applying the R12 calcuation group, the measure for each month is immediately changed to a sum of the measure for the current month and the 11 prior months.

A large number of the pre-defined measures in our primary tabular AS database are simply ratios defined using the DIVIDE() function (effective rates, margins, etc.). When Power BI (or Excel) grand totals such a measure in a report (no calculation group involved), it is smart enough to sum the numerator and denominator and then do the division (because you can't just add up the result of the division on each detail line). This is really a wonderful feature that takes a big headache out of reporting.

But the same does not seem to happen with a measure aggregated using my rolling 12 month calculation. Instead it simply adds the result of the division for each month. I am wondering if I am doing the calculation in a way that confuses the engine. Here is the DAX from the calculation group for a rolling 12 calculation:

CALCULATE (
SUMX (
VALUES ( 'Date'[Date]),
SELECTEDMEASURE ()
),
PARALLELPERIOD(DATESINPERIOD ( 'Date'[Date], MAX ( 'Date'[Date] ), -12, MONTH ), -1, MONTH)
)

I can create specific R12 calculated measures for each ratio measure (by manually applying the above calculation to the numerator and to the denominator separately and then dividing) but we have a lot of them and it is a shame to create so many extra measures in a piecemeal fashion just because calculation groups do not appear to work for these measures.

Thanks in advance!

[KeithDunn · Accepted solution]

I finally solved it on my own. What I was looking for was a way to get DAX to follow the default aggregation for the measure in the database rather than simply summing everything. I finally realized that CALCULATE does this if there is no instruction to do otherwise. The correct code for a rolling 12 where the 12-month period ends with the previous month (to leave out a partial current month) is:

CALCULATE (
SELECTEDMEASURE(),
PARALLELPERIOD(DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH ), -1, MONTH)
)

If you want to include the current month in the 12-month period, just take out the PARALLELPERIOD:

CALCULATE (
SELECTEDMEASURE(),
DATESINPERIOD('Date'[Date], MAX('Date'[Date]), -12, MONTH )
)

I am not sure why the original developer was forcing a SUMX only in the R12 calculation group item but it may have been due to an earlier bug. Anyway it works fine now. 

Thanks!

[amitchandak]

@tenbrooks10 , If you get measure like divide([M1], [M2])

then in this, it will not sum M1 and M2 and then divide. This will sum up divide([M1], [M2]) 

The information you have provided is not making the problem clear to me. Can you please explain with an example.

Appreciate your Kudos.

[KeithDunn]

@amitchandak  Thank you for pointing out that many people may not intuitively understand that directly summing up DIVIDE([M1], [M2]) is almost univerally not the desired aggregation of a ratio and is not the default behavior of Analysis Services.

Here is an example of a PowerBI table showing three measures from a tabular database summed by a customer number. The first column is the customer number but for the moment I will show the measures without the headings:

You can verify with a calculator that the totals for the first two columns are correct but something is wrong with the third column. It should add up to $1,379, right? Has Power BI made a mistake?

Now let's show the same table with headings:

Looking at the headings you can see that the third column is a calculated measure that is:

DIVIDE([Sales Amount], [Qty Sold])

For this type of measure, the correct calculation for the total line is DIVIDE (SUM([Sales Amount]), SUM([Qty Sold])). It is certainly not SUM(DIVIDE([Sales Amount], [Qty Sold])). (As an aside, some may be tempted to think that the Total could be AVERAGE([Avg Price Per Unit]) but this is $460 and is also wrong).

In designing the tabular database, I did not have to tell it to do this total right for dozens of ratio measures. It just knew to do it. For someone like me who has dealt with this issue in report writers for more years than I will admit, this intelligence in the calculation engine is solid gold.

Now, let's take just the customer on the first line (103915), break the numbers down by month and apply a Rolling 12 calculation group item to it to get a smoothed trendline metric (see my previous post for the DAX of the calculation used by the calculation group item):

Each line is now the sum of the twelve months ending in [Month]. Since the first table above was the sum of the measures for the 12 months ending in February 2023, you can see that the last line of this table matches the numbers for the same customer in the table above in the first two columns but the Avg Price Per Unit does not!  The prices are ridiculouly wrong. The calculation group appears to be doing SUM([Avg Price Per Unit]) rather than DIVIDE(SUM([Sales Amount]), SUM([Qty Sold])). 

In Summary, my question is 1) is there an error in calculation groups (and will it be fixed) or 2) is there an error in my DAX or my configuration of the database?

Thanks in advance of your interest and advice.