Average in total row Rankx

[pfinanciero]

Hi its me again 🙂 

I did the following formula to rank some data with two (2) filters:

RANKX(

FILTER(Prueba,Prueba[Fecha] = EARLIER(Prueba[Fecha])

&& Prueba[Indicador] = EARLIER(Prueba[Indicador])),

Prueba[Valor],,DESC,Dense

I need to calculate the average in the total row of RANKX, it is posible? Let me know if you need more information or data.

Thanks!

[V-lianl-msft · Accepted solution]

Hi @pfinanciero ,

Try to create 2 measures:

Measure_ranking = AVERAGEX(VALUES('Table'[Indicador]),AVERAGE('Table'[rank]))
Measure_valor = AVERAGEX(VALUES('Table'[Indicador]),AVERAGE('Table'[Valor]))

Best Regards,
Liang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[smpa01]

@pfinanciero  provide sample data and expected output

[pfinanciero]

Hi @smpa01 ,

I have the following data chart:

In the firt column "Razón social" are the names of the diferents companies, the secod column shows the type of the rate or indicator (if its an asset or a sale) for this example, the third column are the values and the last one is the year.

with this information I build the following table: 

As you can see I insert an slicer with "Razón social" because the idea of the chart is to shows the values and raking of each company selected.

I need to calculate in the total row of the column Ranking the average of the results of that year, for example:

Fecha	2018		2019		2020		Total
Indicador	Valor	Ranking	Valor	Ranking	Valor	Ranking	Valor	Ranking
Activos	133.802	71	131.801	90	145.185	81	410.788	81
Ventas	136.124	69	124.006	101	124.682	94	384.992	88
Total	BLANK	70	BLANK	96	BLANK	88	BLANK	85

[AlexisOlson]

The basic pattern for this will look like:

AVERAGEX (
    SUMMARIZE ( Prueba, Prueba[Fecha], Prueba[Indicador] ),
    [Ranking measure]
)

Note that if there is only one Fecha and Indicador in the filter context, you're only averaging over one value, so the result is the same as [Ranking measure].