Adding different level aware id together into single Summary table visual

I have 2 data tabled: first on abandoned account with id, status, spend and second on active account with id, status, spend. The tables are left joined on user who is now owner of the account (sample data shown below). My use case is to show combined spend per user.

user abandoned account abandoned spend non abandoned account abandoned spend status
A 1 $10 NULL $0 abandoned
A 2 $20 NULL $0 abandoned
A NULL $0 3 $5 non-abandoned
Total $30 $5

I calculated:
abandoned spend as sum(spend, [abandoned account]) / count(spend, [abandoned account])
and non abandoned spend as sum(spend, [non_abandoned account]) / count(spend, [non_abandoned account]).
This gets my per row sum(spend) and Total row numbers accurate.

Now, I want to have combined spend per user, so user A should have sum(spend) Total row = $35. But, when I try to put all three calculations into single table visual (or even try to visualize combined spend standalone), I get error The Level Aware Calculation (LAC) aggregate functions inside one visual aggregate functions should always share the same grouping key

As of now I have tried, both give me same error mentioned above:
1/ abandoned spend + non_abandoned spend
2/ (sum(spend,[abandoned account]) + sum(spend,[abandoned account])) /
(count(spend, [non abandoned account]) + count(spend, [non abandoned account]))
3/ sum(abandoned spend + non abandoned spend,[abandoned account id, non abandoned account id]) / (count(abandoned spend, [abandoned account id]) + count(non abandoned spend, [non abandoned spend]))

Any suggestions of how I can get all three values in the same Summary table? Thank you in advance for your time!

Hello @Siddhi_Patil - Did you try out simple ifelse construct to build your abandonment_spend and non_abandonment_spend calculated fields. That should solve the problem. I tried to replicate the calculations and that works perfectly for me. Please see the below snapshot. Hope this helps!

Did my suggestion help you in resolving your query? If yes, would request you to mark the post as “Solution”. This will help the community to find guidance and answers to similar question. Thank you!