Fixed Average in Matrix Table to use as a Measure

[jchungfluentco]

Hi,

I am trying to get the average of a column in a matrix fixed so that I can use it as a static metric to divide against another measure in each row. The end result is to try to get Index_1, the index of sales against number of leads sent out.

To clarify, I created a matrix from TABLE_1 that has the following:

Column_1 = 'Sales'
Column_2 = 'Leads'
[Measure] Conversion Rate = SUM('Sales')/SUM('Leads')
Avg. Conversion Rate = AVERAGEX('TABLE_1', [Conversion Rate])
Index_1 = CALCULATE(DIVIDE([Conversion Rate],[Avg. Conversion Rate]))

The problem here is that I can't get the Avg. Conversion Rate of all rows in the matrix to become fixed so that I can divide it by the conversion rate, resulting in Index_1. What ends up happening is that when I drag Index_1 into the matrix, it results in 1 all the way down.

Does anyone have a solution to this? Is a SUMMARIZE table the best way?

Thanks!

[MFelix · Accepted solution]

Hi @jchungfluentco,

The measures are calculated based on context so based on that if you place a measure in a visual with some columns to make aggregations the calculations are made based on that aggregation, in order to achieve what you need, you have to take the Avg.Conversion Rate context to give you full result over the all table in this case use the following measure:

Avg. Conversion Rate =
CALCULATE (
    AVERAGEX ( 'Sales_lead'; [Conversion Rate] );
    ALLSELECTED ( Sales_lead )
)

I use the formula ALLSELECT if you want to calculate over the filtered values, for instance with a slicer if you want to make this a fixed variable indenpent of any slicer of filter  you put on your report you should use:

Avg. Conversion Rate =
CALCULATE (
    AVERAGEX ( 'Sales_lead'; [Conversion Rate] );
    ALL ( Sales_lead )
)

Be also ware that to have better performance you should try and use the columns and not the full table on your AVERAGEX and on your ALLSELECTED/ALL formulas.

Result below:

Regards,

MFelix