Filtering and returning average across multiple tables

[Simo2024]

Hi,

I am new to power BI, and facing some issues.
I have a sumary table with unique IDs and a textual description of the said IDs, and then multiple tables with the same IDs and other values.
the relation ship between the sum table and the rest of the tables is one to many.
I am trying to make a report that filters all the tables and calculate the average, min and max of each unique ID.
Does anybody have any suggestions on how to approach this.

I have tried calculating the average for each table and then using the related function to fetch the values but it doesn't seem to work and my understanding is that the one to many relation is the reason for this!

Thanks!

[v-junyant-msft · Accepted solution]

Hi @Simo2024 ,

Please check if this is what you need?
Here are my sample dataests:

I appended these tables to a new table in Power Query:

MAX = MAX('Append1'[Value])

MIN = MIN('Append1'[Value])

AVG = AVERAGE(Append1[Value])

The results are as follows:

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

[Simo2024]

Hi,
Thank you, this works.  I have however one worry as I currently have small number of tables and the plan is to keep adding more and more tables, would combining all the tables create some performance issues in the future?
Kind regards

[v-junyant-msft]

Hi @Simo2024 ,

In my opinion, there is definitely an impact on performance, but since DAX is simpler, I don't think it will be a big impact.
You can try using performance analyser to check the performance of your reports.

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

[zenisekd]

Create a table visual in Power BI destkop and insert:
1. ID column from the unique table (Fact table)

2. Other Dimension values, that interest you. e.g. Qty, Sales, etc. When inserted into the visual, by default they are summed (SUM). If you however right-click on the field in the settings, you can select other way of aggregation - Min, Max, Average, and so on...

It is not necessary to create measures.

[Simo2024]

Hi,
Thank you for your reply, this approach fetches data from one single table at a time and not from across all tables at once!

[zenisekd]

Could you elaborate?