Forum Discussion

mahawkins3's avatar
mahawkins3
Icon for Helper I rankHelper I
9 years ago
Solved

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 ...
Need Help
  • Anonymous's avatar
    Anonymous
    9 years ago

    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)
	)
)
TomMartens's avatar
TomMartens
Icon for Super User rankSuper User
9 years ago

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

  • mahawkins3's avatar
    mahawkins3
    Icon for Helper I rankHelper I
    9 years ago

    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?

      • mahawkins3's avatar
        mahawkins3
        Icon for Helper I rankHelper I
        9 years ago

        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.