SUM not equalling total when turning column chart into a stack

[mahawkins3]

Hi all,

I'm having an issue whereby adding a variable to the "Legend" of a visualisation gives me numbers that don't add up to the total. Here's the version without the legend (these numbers are correct):

And here's the version with a variable added to the legend of the visualisation. I have changed nothing else about the visualisation. As you can see, the sums of the stacks add up to considerably more than the totals above.

It may be worth mentioning that the Value being used here is a measure (total revenue in the Fees table divided by unique customer IDs):

Any idea what may be going wrong?

Thanks in advance

[TomMartens]

Hey,

slighly rephrasing what @KHorseman already explained, assume you have the following simple dataset

Brand	Customer	Value
A	C1		400
B	C2		50
B	C3		50

Without using Brand as variable in the legend you divide 500 by 3 (distinct customer).

Adding Brand as legend you have two divisions divisions because you have to segments

A) 400 / 1

B) 100 / 2

Hope this also adds to the understanding, arithmetics with DistinctCount are special 😉

Regards

Tom

[mahawkins3]

Thanks both.

In that case, I'm not totally sure of the best way to write this formula. One option would be to write a separate measure for each item in the stack then include all of them as separate values, but that seems a little inefficient. Is there a way to achieve this within a single measure?

[KHorseman]

This is probably the pattern you're looking for: https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/

[mahawkins3]

Unfortunately that method achieves the opposite of what I'm looking for. It makes the total add up to the sum of the splits (i.e. it keeps the incorrect splits and makes the total incorrect too) rather than the reverse. With the measure I initially wrote, my totals were correct, but each element of the stack was coming out too high.

[KHorseman]

Oh I missed that you said the un-split version was actually correct. So how do  you know the splits are each incorrect? Are you saying it's incorrect just because it doesn't add up to the un-split version?

[mahawkins3]

Yes, they're incorrect because the splits don't add up to the un-split version (I have already verified that the un-split version is correct just by running the numbers manually). When I say "incorrect", I mean that my formula must be wrong for the context in which I'm using it; presumably the numbers are correct based on how I've told Power BI to calculate them!

As I mentioned above I could probably solve this in an inelegant fashion by just creating separate measures for each element of the stack filtering each category separately, but that's a) messy and b) not robust against future changes in the categories. What I'm looking for is a more elegant solution that keeps the work within a single measure and won't break if new categories are added in the future.

Thanks

[KHorseman · Accepted solution]

Well your formula is a ratio so I wouldn't expect each segment to add up to equal the unsegmented total as a sum. Unless your formula is supposed to the count of all user IDs regardless of segment? Like for each segment you want to divide the sum of the fee value against the count of all user IDs not just the IDs with fees in that segment?

If that's the case then I believe your measure should be

DIVIDE(
	SUM(Fees[Value]),
	CALCULATE(
		DISTINCTCOUNT(Fees[User_ID]),
		ALL(Fees)
	)
)

[mahawkins3]

Thanks - that solution seems to be along the lines of what I'm looking for (I had tried playing around with ALL before, but was probably getting the syntax wrong). Only complication is I have other filters that need to remain on, so I think my particular context requires an ALLEXCEPT rather than an ALL.

[KHorseman]

Fortunately, once you see the pattern using ALL it's easy to adapt to ALLEXCEPT. 🙂

[KHorseman]

The formula for the version without the legend is the same as the formula with the legend. With the legend, the same formula is calculated once for each element in the legend. Without it, the formula is calcualted for the entire set. Nowhere in your formula do you say that it should calculate for each element in the legend then add up the subtotals.