Overcoming Non-Additivie Grand Total Error

[LABrowne]

Hello there,

I am new to dax and I have a set of formulas that work but the grand total is wrong:

First is the main formula:

(1)

Net Earned Premiums =

SUMX(

    VALUES(CarrierContract[ContractId]),(([Net Written Premiums]/[Policy Period])*[Days On Cover]))

Then a breakdown of the other formulas:

(2)

Net Written Premiums =

([Gross Written Premium]*0.775)

(3)

Policy Period =

DATEDIFF(MAX(Policy[InceptionDate]),MAX(Policy[ExpiryDate]),DAY)

(4)

Days On Cover =

VAR Days_Since_Inception = DATEDIFF( MAX( (Policy[InceptionDate])), TODAY(), DAY)

RETURN IF(Days_Since_Inception <= [Policy Period], Days_Since_Inception, [Policy Period])

Seems to be working at the row level but not totallin correctly?

Any help would be great!

[sjoerdvn]

You haven't mentioned what the "row level" is in your report. Is it time periods, like months?
I see two related issues in the measures that make them not useable in certain contexts, like grand totals:
First the use of the today function. If you are reporting periods you would want to use the maximium date of the current date context instead of today. Also, for determining the [Days on cover] you would want to check if the inception date is on or after the minimum date of the reporting period.

[123abc · Accepted solution]

It looks like you're dealing with a common issue in DAX (Data Analysis Expressions) where the total at the grand total level is not aggregating correctly. This is often referred to as the "non-additive grand total error."

The problem usually arises when you have measures that rely on row context, and the aggregation at the total level does not correctly consider this context. In your case, it might be related to the use of the MAX function in the Policy Period measure.

Here are a few suggestions to help you overcome this issue:

1. Use AVERAGEX for Policy Period: Instead of using MAX to get a single value for Policy Period, you can use AVERAGEX to iterate over the rows and calculate the average. Since there's only one row context for Policy, it will effectively return the same value at the row level and the total level.

Policy Period =
AVERAGEX(ALL(Policy), DATEDIFF(Policy[InceptionDate], Policy[ExpiryDate], DAY))

Modify the Net Earned Premiums Measure: Try modifying the Net Earned Premiums measure to use SUMX over the Policy table instead of the CarrierContract table.

Net Earned Premiums =
SUMX(
VALUES(Policy[PolicyId]),
([Net Written Premiums]/[Policy Period])*[Days On Cover]
)

1. This assumes there is a relationship between the CarrierContract and Policy tables.

2. Use VALUES in Days On Cover: Modify the Days On Cover measure to use VALUES for Policy. This ensures that the context transition from row context to filter context is handled correctly.

Days On Cover =
VAR Days_Since_Inception = DATEDIFF(MAX(Policy[InceptionDate]), TODAY(), DAY)
RETURN IF(Days_Since_Inception <= [Policy Period], Days_Since_Inception, [Policy Period])

becomes

Days On Cover =
VAR Days_Since_Inception = DATEDIFF(MAX(Policy[InceptionDate]), TODAY(), DAY)
RETURN IF(Days_Since_Inception <= VALUES(Policy[Policy Period]), Days_Since_Inception, VALUES(Policy[Policy Period]))

Try incorporating these changes and see if they resolve the issue. Always remember to test thoroughly with your data to ensure the correctness of the results.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[LABrowne]

Hi,

I want to say you are a hero!

Simply just modifying the net earned premiums has fixed the issue!

Thanks!