Forum Discussion
Calculating averages on row level in table visualisation
Hi,
I'm a bit stuck with getting correct averages in a table visual.
My data source (simplified) looks a bit like this:
| Date | Channel | Platform | Cost | Transactions | Cost per Transaction |
| 1 | Paid Search | 1000 | 15 | 66.67 | |
| 1 | Paid Search | Bing | 500 | 6 | 83.33 |
| 1 | Paid Social | Meta | 700 | 5 | 140.00 |
| 1 | Paid Social | 250 | 0 | ||
| 1 | Paid Social | Snapchat | 100 | 0 | |
| 1 | Direct | Direct | 20 | ||
| 1 | Organic | Organic | 17 | ||
| 2 | Paid Search | 1200 | 14 | 85.71 | |
| 2 | Paid Search | Bing | 600 | 8 | 75.00 |
| 2 | Paid Social | Meta | 750 | 6 | 125.00 |
| 2 | Paid Social | 200 | 1 | 200.00 | |
| 2 | Paid Social | Snapchat | 75 | 1 | 75.00 |
| 2 | Direct | Direct | 18 | ||
| 2 | Organic | Organic | 14 |
Cost per Transaction is calculated as DIVIDE(SUM(Cost)/SUM(Transactions)). This works perfectly fine on row level.
However, when creating a table visual (using average as aggregation), I get this:
| Channel | Platform | Cost | Transactions | Cost per Transaction (what I have now) | Cost per Transaction (wanted solution) |
| Paid Search | 2200 | 29 | 75.86 | 75.86 | |
| Paid Search | Bing | 1100 | 14 | 78.57 | 78.57 |
| Paid Social | Meta | 1450 | 11 | 131.82 | 131.82 |
| Paid Social | 450 | 1 | 200.00 | 450.00 | |
| Paid Social | Snapchat | 175 | 1 | 75.00 | 175.00 |
| Direct | Direct | 0 | 38 | ||
| Organic | Organic | 0 | 31 |
The first cost per transaction column is what I have now, but that one is incorrect since it doesn't take into account rows where there were cost, but no transactions. What I need is a calculation like the one in the most right column of this example, which calculates the average on row level in the table visualisation. Anyone who can help me out?
Still works on all levels, but needs to be a measure.
1 Reply
- lbendlin
Super User
Still works on all levels, but needs to be a measure.