Average in subtotal and grand total of a RANKX

[pfinanciero]

Hi all,

I have a database with the financial results of several companies by quarters from 2018 to 2021, I have created the following measure in order to give a ranking to each company according to the indicator that is evaluated (for this example "Ventas" and " Activos"):

Measure =

VAR Ratio =

IF(

ISBLANK(

MAX(Ratios[Valor])), BLANK(),

RANKX(

FILTER(ALL(Ratios), Ratios[Fecha] = MAX(Ratios[Fecha])

&& Ratios[Indicador] = MAX(Ratios[Indicador])),

CALCULATE(

SUMX(Ratios, Ratios[Valor] + Ratios[NIT] / 1000000000000)

),,DESC,Dense

)

)

VAR ColumnaX =

ADDCOLUMNS(Ratios,"Rank",Ratio)

RETURN

IF(

HASONEVALUE(Ratios[Fecha]),

Ratio,

AVERAGEX(ColumnaX,[Rank])

)

Everything was going well until I tried to calculate in the subtotals and grand total the average of the rankings or qualifications that each company has had.

For example, in the table below I selected a specific company to check the sub totals and grand totals:

As you can see, the formula is not calculating the averages of the rankings for each year and quarter. Do you know if there is a solution for this, in what part of the formula I am falling?
Thanks

[Whitewater100]

Hi pfinancero:

I have proposed file for your solution but am not sure how to attach it.  This is the data model and measures. I tried to use your figures where I could.

Activos Sum = SUM(Fact_Data[Activos Score])

Row CT Activos = COUNTROWS(Fact_Data)

Total Activos Avg = DIVIDE(Fact_Data[Activos Sum], [Row CT Activos],"-")

Avg Activos =

IF(ISFILTERED(Dates),

DIVIDE( AVERAGEX(VALUES(Dates[Year]),

Fact_Data[Activos Sum]), [Row CT Activos]), [Total Activos Avg])

Rank Activos =

IF(ISFILTERED(Dates),

RANKX(ALLSELECTED(Dates[Quarter & Year]),

[Avg Activos], ,DESC, DENSE))

 I hope this helps!

[VahidDM]

Hi @pfinanciero 

Can you share a sample of your data in a text format or share your PBIX file?

Appreciate your Kudos!!
LinkedIn: www.linkedin.com/in/vahid-dm/

[pfinanciero]

Hi @VahidDM @Whitewater100 thanks for your answer, let me give more info about the data.

I cant share the pbi file, then i will try to give a short description of the file.

All financial data comes from the following table called "Consolidado":

As you can see, the table is composed of the columns of dates, "Grupo" (company grouper), "Cuenta puc"  (accounting account id), "Detalle puc" (accounting id description), "NIT" (company id) and "Valor ($ M)" (they are the values). 

From the id of the ledger account and the value column, several indicators are calculated, including the value of sales "Ventas" and assets "Activos" per quarter for each year.

From those calculated indicators I created the following table using the SUMMARIZE formula, the name of this table is "Ratios":

As you can see, this table has an additional column with the name of the company (related from the NIT), the column "Indicador" which in this case are sales and assets, the column "Valor" which are the values ​​of the indicator of each company per quarter. (Please ignore the "Prueba" column)

The ranking of companies by indicator is calculated from the Ratios table, taking into account the quarters of each year. 

The formula that I used to calculate the ranking of each company by indicator works well:

Measure =

VAR Ratio =

IF(

ISBLANK(

MAX(Ratios[Valor])), BLANK(),

RANKX(

FILTER(ALL(Ratios), Ratios[Fecha] = MAX(Ratios[Fecha])

&& Ratios[Indicador] = MAX(Ratios[Indicador])),

CALCULATE(

SUMX(Ratios, Ratios[Valor] + Ratios[NIT] / 1000000000000)

),,DESC,Dense

)

)

VAR Tabla =

ADDCOLUMNS(Ratios,"Rank",Ratio)

RETURN

IF(

HASONEVALUE(Ratios[Fecha]),

Ratio,

AVERAGEX(Tabla,[Rank])

)

Need: the problem I have is that I need (if possible) to calculate the average of the rankings in the subtotals and grand totals of the following table:

In the previous table I selected a particular company to see its ranking in the different quarters and year by indicator. As you can see in the rows of sub totals, grand totals and in the total column it is not calculating the average of the rankings obtained by the company. For example, in 2018 the average ranking should be 21 and not 10 for the "Activos" indicator, and for the "Ventas" indicator it should be 26 and not 15.

I think my problem is somewhere in this section of the formula but i couldn't find the error:

IF(

HASONEVALUE(Ratios[Fecha]),

Ratio,

AVERAGEX(Tabla,[Rank])

)

Thank you very much for your collaboration