Variance % being Counted Incorrectly

DAX DAX Calculations
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])
    )

Enterprise%20DNA%20Expert%20-%20Small

3

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

image

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

4

@chris.turnbull

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.

Enterprise%20DNA%20Expert%20-%20Small

5

Hi Nick

That’s me being a pillock and not concentrating.

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

image