Average from KPI column in several tables

[joelantero]

Hi,

I'm a beginer in DAX, so have some patience 🙂

I have several tables  each one with a KPI column.

I want to create an average from the several tables KPI so I can obtain a unique value from these several KPI's. How can I do this?

Is this possible? I already serched for other similar questions, but none give the necesary  knowledge

Thanks for you time

all the best

[v-huizhn-msft · Accepted solution]

Hi @joelantero,

What's your table structure? Based on mu understanding, I create the following two sample tables. The method is same if you have multiple tables.

TableATableB
You want to calcualte the average of the two tables KPI, right? If it is, you create a new table to union all KPI columns in different tables. Please click "New Table"-> under Modeling on Home page , type the formula.

NewTable = UNION(SELECTCOLUMNS(TableA,"KPI",TableA[KPI]),SELECTCOLUMNS(TableB,"KPI",TableB[KPI]))

NewTable
Then you create a measure based on the KPI in NewTable.

Average = AVERAGEX(ALL(NewTable),NewTable[KPI])

Create a card visual to display the result.

If there are many tables, please use the similar formula.

NewTable = UNION(SELECTCOLUMNS(Table1,"KPI",Table1[KPI]),SELECTCOLUMNS(Table2,"KPI",Table2[KPI]),SELECTCOLUMNS(Table3,"KPI",Table3[KPI]),SELECTCOLUMNS(Table4,"KPI",Table4[KPI]),,,,)

Best Regards,
Angelia