admiral_pelican
New Member
- Joined
- Jan 18, 2019
- Messages
- 4
Hello,
I have a report with a date slicer that returns, by sales manager, by sales rep, by account, the current period revenue and prior year revenue for the sliced date range. I need to add a column that counts the number of accounts with revenue in the current period. The data source is such that adding a helper column to it almost certainly won't help. My approach so far is to insert calculated field, IF(Total Revenue>0,1,0), which returns a 1 next to each advertiser with current period revenue. My problem is that, for each sales rep, it doesn't return a subtotal of the number of accounts under him/her with a 1 next to them -- it just returns a 1, and same for sales manager and same for grand total. I need the calculated field to essentially sumifs itself at the subtotal and grand total levels.
Can anyone help me return a correct count subtotal in a Pivot Table calculated field?
Please assist.
<tbody>
</tbody>
I have a report with a date slicer that returns, by sales manager, by sales rep, by account, the current period revenue and prior year revenue for the sliced date range. I need to add a column that counts the number of accounts with revenue in the current period. The data source is such that adding a helper column to it almost certainly won't help. My approach so far is to insert calculated field, IF(Total Revenue>0,1,0), which returns a 1 next to each advertiser with current period revenue. My problem is that, for each sales rep, it doesn't return a subtotal of the number of accounts under him/her with a 1 next to them -- it just returns a 1, and same for sales manager and same for grand total. I need the calculated field to essentially sumifs itself at the subtotal and grand total levels.
Can anyone help me return a correct count subtotal in a Pivot Table calculated field?
Please assist.
SM | AE | Advertiser | Current Revenue | # Accounts |
SM1 | 244,542 | (sum of all SM's accounts) | ||
SM1 | AE1 | 6,745 | 2 | |
SM1 | AE1 | Advertiser 1 | - | - |
SM1 | AE1 | Advertiser 2 | 3,519 | 1 |
SM1 | AE1 | Advertiser 3 | - | - |
SM1 | AE1 | Advertiser 4 | - | - |
SM1 | AE1 | Advertiser 5 | 3,226 | 1 |
<tbody>
</tbody>