Variance % being Counted Incorrectly


#1

DNA%20Variation DNA - Variance.pbix (52.9 KB)

Hi

Please see attached.

The Category and Sub Total Category pct’s are incorrect due to Brand B having a Target but no Actual Total. This Target is being counted in the Variance at Sub Total and Total Level but shouldn’t be. How do I tell the Variance Measure to ignore any Target that doesn’t have an Actual Total.

Any help gratefully received.

Thanks

Chris


#2

@chris.turnbull,
Let’s see if this is what you had in mind:
Variance%2C%20ignore%20if%20no%20target

If so, here’s the formula:

Total Target = 
    SUMX(
        FactTarget, 
        IF ( ISBLANK([Total] ),
        BLANK(), 
        FactTarget[Target])
    )