Overall % Incorrect Value


#1

Hi Sam,

I’ve run into a little issue where i don’t seem to be getting the correct overall % value.

It works correctly for the individual ones but not for the overall grand total row (See screenshot below)

% Completed Target Achieved =
VAR completerisk =
    DIVIDE (
        [All Adviser Completed Premium],
        [All Adviser Completed Premium Target],
        0
    )
VAR completeinvest =
    DIVIDE (
        [Adviser Selected Completed FUM (All)],
        [All Adviser Completed FUM Target],
        0
    )
VAR TOTALPREMIUMFUM =
    CALCULATE (
        SUM ( 'Access DB Completions'[Annual Premium/FUM] ),
        FILTER ( 'Access DB Completions', 'Access DB Completions'[Excluded FUM] = "No" )
    )
VAR TARGETTOTAL =
    CALCULATE (
        SUM ( 'Access DB Targets'[trgtTargetValue] ),
        FILTER ( 'Access DB Targets', 'Access DB Targets'[trgDescrip] = "Completion" )
    )
VAR OVERALLPERCENTAGE =
    DIVIDE ( TOTALPREMIUMFUM, TARGETTOTAL, 0 )
RETURN
    SWITCH (
        TRUE (),
        [New Business Lodgement Type] = "Investment", completeinvest,
        [New Business Lodgement Type] = "Risk", completerisk,
        OVERALLPERCENTAGE
    )

image

I feel it is an issue with the 'OVERALLPERCENTAGE ’ variable calculation

Thanks


#2

Bit going on here so just want to check a few things.

Have you been able to break these out individually and see what the two variables actually return on there own?

I think this would be the very first thing to do. As they are in variable it’s obviously hard to know, so I always break them out and test what the individual part is calculating at a particular context.

It’s likely that TOTALPRE… & TARTGETTOT… aren’t actually working out the same totals as show in the table.

Can you give this a go and see what you come up with.

Chrs


#3

Hi Sam,

I followed your recommendation and was able to fix the issue.

Thanks


#4

That’s great. Chrs