Calculating averages on row level in table visualisation

[daniel_t_93_]

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	Google	1000	15	66.67
1	Paid Search	Bing	500	6	83.33
1	Paid Social	Meta	700	5	140.00
1	Paid Social	Pinterest	250	0
1	Paid Social	Snapchat	100	0
1	Direct	Direct		20
1	Organic	Organic		17
2	Paid Search	Google	1200	14	85.71
2	Paid Search	Bing	600	8	75.00
2	Paid Social	Meta	750	6	125.00
2	Paid Social	Pinterest	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	Google	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	Pinterest	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?

[lbendlin · Accepted solution]

Still works on all levels, but needs to be a measure.