Forum Discussion
SUM not equalling total when turning column chart into a stack
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
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) ) )
10 Replies
Hey,
slighly rephrasing what Anonymous 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
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?This is probably the pattern you're looking for: https://powerpivotpro.com/2012/03/subtotals-and-grand-totals-that-add-up-correctly/
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.
