Variance % being Counted Incorrectly

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


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.



Let’s see if this is what you had in mind:

If so, here’s the formula:

Total Target = 
        IF ( ISBLANK([Total] ),


Hi Nick

Thanks for the reply.

Sorry to have not replied earlier but I didn’t get a message saying I had a reply.

The actual solution for me is below:-

Values Measure = 
            CALCULATE([Total Target], FactTarget[Brand] 
            IN VALUES(FactData[Brand]))

Which gives me


The B Target had to be considered and I wanted it to be displayed as well.


No problem at all, as long as you got your solution. One thing to keep in mind ( and might not even be an issue) is that at the total level the numbers can easily be misunderstood. For example, if a user looks at the Grand Total row and sees Total of 44, Total Target of 70 and the Total Variance and % do not match those figures. Just something to keep in mind when looking at the final output. Like I said, might not be an issue for your end users who know your data.


Hi Nick

That’s me being a pillock and not concentrating.

Thinking about it I was actually correct in the first place.