Use Calculation in Matrix Total

[Anonymous]

Hi all,

I have a simple calculated column that is dividing two column by each other:

Conversion Rate = DIVIDE('Campaigns Data'[Conversions],'Campaigns Data'[Unique Pageviews])

I've put the results into a matrix and they're fine - the calculation is set to Sum, but it doesn't really matter as the data table has only a single row per day. However, the Total row is adding up all of the results of the rows - what I'm after though is for the total to be a sum of all the conversions divided by a sum of all the unique pageviews as per the Conversion Rate calculation. 

Can anyone suggest if this is doable?

TIA,

SamB 

[v-yuta-msft]

Hi SamBerger ,

Based on your description, it seems like there're three different columns in your table named 'conversions ', 'unique pageviews' and 'Conversion Rate calculation', right? Could you share more details about your raw data and requirement?

Regards,

Jimmy Tao

[Anonymous]

Hi Jimmy,

Thanks for the reply. Yes, that is correct - see table below. This is the dataset in its entirety. 

Date	Day of Week Name	Unique Pageviews	Week Label	Date Sorter	Conversions	Conversion Rate
19/11/2018	Monday	31381.0	Last Week	1	4576	14.6%
20/11/2018	Tuesday	32044.0	Last Week	2	4787	14.9%
21/11/2018	Wednesday	30498.0	Last Week	3	4491	14.7%
22/11/2018	Thursday	29649.0	Last Week	4	4332	14.6%
23/11/2018	Friday	25553.0	Last Week	5	3701	14.5%
24/11/2018	Saturday	15765.0	Last Week	6	2425	15.4%
25/11/2018	Sunday	17179.0	Last Week	7	2639	15.4%
12/11/2018	Monday	32600.0	Week Before	1	4855	14.9%
13/11/2018	Tuesday	31677.0	Week Before	2	4689	14.8%
14/11/2018	Wednesday	29508.0	Week Before	3	4343	14.7%
15/11/2018	Thursday	28203.0	Week Before	4	4167	14.8%
16/11/2018	Friday	24955.0	Week Before	5	3689	14.8%
17/11/2018	Saturday	15794.0	Week Before	6	2355	14.9%
18/11/2018	Sunday	17322.0	Week Before	7	2603	15.0%

So in terms of the matrix, Day of Week Name is going in Rows, Week Label in Columns and Conversion Rate (calculated by dividing Converstions into Unique Pageviews) in Values.

In terms of results, the Week Before 'Total' would be 14.83% ( Total Conversion for 'Week Before' = 26701 / Total Unique Pageviews for 'Week Before' = 180059).

Let me know if you need any further info.

Thanks,

Sam

[Anonymous · Accepted solution]

FYI for anyone reading this. I have solved this by creating three measures:

Total uPVs = SUM('Data'[Unique Pageviews])

Total Conversions = SUM('Data'[Conversions]

Conversion Rate_Proper = DIVIDE ([Total Conversions],[Total uPVs])

Conversion Rate_Proper then goes into the value field in the matrix.

Works a charm.

Sam