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!
