How to get minimum of averages

[mujahid1022]

Hello,

Following is my data set where I have captured the average of a column. The average colum is created using a new measure column feature with the AVERAGE function as : AVG_FYTD = AVERAGE(Reporting_KPI_DataNoSecurity[FYTDGIR])

As a next step I want to capture the minumum of this average values. I tried creating a new measure based on the calculated Average column using: MIN_FYTD = MIN(Reporting_KPI_DataNoSecurity[AVG_FYTD]). This does not allow me to capture the Minimum out of AVG_FTYD column.

Any suggestions on achieving this will be great help.

Thank you,

Mujahid

[Anonymous · Accepted solution]

Hi @mujahid1022,

You can use dax to create a summary table, then use minx function to get the min average.

Sample:

Measure:

Avg = AVERAGE(Sheet1[Amount])

MinAvg = MINX(SUMMARIZE(ALL(Sheet1),Sheet1[Name],"Avg Amount",AVERAGE(Sheet1[Amount])),[Avg Amount])

Visual:

Reference:

SUMMARIZE Function (DAX)

MINX Function (DAX)

Regards,

Xiaoxin Sheng

[Greg_Deckler]

So, AVG_FTD is a column or measure?

[mujahid1022]

I have created this as a measure.

[Greg_Deckler]

So, the problem that you have is one of context. The context in which you have individual averages is only present in your visual. I would think that what you would have to do is create a table with your 3 kpis and relate it to your other table. Then create a column in that table and calculate the averages. Then, you could create a measure to get the min of that column.