Forum Discussion
Helper ISUM not equalling total when turning column chart into a stack
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) ) )
Helper IThanks 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/
- 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.
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