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)
	)
)
mahawkins3's avatar
mahawkins3
Icon for Helper I rankHelper I
9 years ago

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

Anonymous's avatar
Anonymous
Not applicable
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)
	)
)
  • mahawkins3's avatar
    mahawkins3
    Icon for Helper I rankHelper I
    9 years ago

    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.

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

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