cancel
Showing results for
Search instead for
Did you mean:
Frequent Visitor

## How to create an input table pivoting field from multiple tables

Hello this is the situation:

I have multiple tables based on Type of school:

Primary school TableSecondary school Table

Then I have a table with the Max People Capacity regardless the Type of school

Daily capacity Table

And at the end I have a Calendar Table

Calendar Table

The tables have these relationships:

Relationships

Moreover I have 2 Measures:

Total no. of People

Tot. no of People =
SUM(Primaryschool_Table[No. of People])
+ SUM(Secondaryschool_Table[No. of People])

and the Availabilty Measure (Max daily Capacity-Tot. no of People)

Availability Measure =
SUM(DailyCapacity_Table[Daily Max people capacity])
-SUM(Primaryschool_Table[No. of People])
-SUM(Secondaryschool_Table[No. of People])

I need to create an input table with "Date", "City" and the associate "Availability" as follow:

I need it as an input table in the data model, in order to create additional Measures based on it (e.g. Sum all city with Availabilty > 40).

Perhaps I was verbose, but it was necessary to make the picture clear. I thank you all of us who can help me!!! I'm trying to solve this since 2 weeks!

1 ACCEPTED SOLUTION

Accepted Solutions
New Contributor

## Re: How to create an input table pivoting field from multiple tables

It'd be an awful lot easier if you merged your primary and secondary tables together...

5 REPLIES 5
Frequent Visitor

## How to create a measure grouping by a field in another table

Hello this is the situation:

I have multiple tables based on Type of school:

Primary school TableSecondary school Table

Then I have a table with the Max People Capacity regardless the Type of school

Daily capacity Table

And at the end I have a Calendar Table

Calendar Table

And the tables have these relationships:

Relationships

Moreover I have 2 Measures:

Total no. of People

Tot. no of People =
SUM(Primaryschool_Table[No. of People])
+ SUM(Secondaryschool_Table[No. of People])

and the Availabilty Measure (Max daily Capacity-Tot. no of People)

Availability Measure =
SUM(DailyCapacity_Table[Daily Max people capacity])
-SUM(Primaryschool_Table[No. of People])
-SUM(Secondaryschool_Table[No. of People])

I need to create an input table with "Date", "City" and the associate "Availability" as follow:

I need it as an input table in the data model, in order to create additional Measures based on it (e.g. Sum all city with Availabilty > 40).

Perhaps I was verbose, but it was necessary to make the picture clear. I thank you all of us who can help me!!! I'm trying to solve this since 2 weeks!

New Contributor

## Re: How to create an input table pivoting field from multiple tables

It'd be an awful lot easier if you merged your primary and secondary tables together...

Frequent Visitor

## Re: How to create an input table pivoting field from multiple tables

Thank you so much @jthomson.

But I do not know if in the actual situation I can do this.

I actually have:

- millions of records

- 6 types of schools tables

- the columns of the different 'type of school table' are different

- in the primaryschool_table there is a single record for any single building, while in the others tables they are grouped by location as in the example

Of course all of tables have 1 column that contains the No. of people (but with different detail aggregation depending on the table).

Do you think that I should create a single enormous Table? (maybe working with the 'group by' feature in query editor regarding the primary school table?)

Super User

## Re: How to create a measure grouping by a field in another table

First, very good post, you presented out all of the information required. Only complaint is could you post sample data in a format that can be copied and pasted? I'm too lazy to type data by hand.

### I have book! Learn Power BI from Packt

Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!

Super Contributor

## Re: How to create a measure grouping by a field in another table

You can have the sample data for all 4 tables in an excel and attach the file here.

Thanks

Raj

## Helpful resources

Announcements

#### New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

#### Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

#### Power Platform World Tour

Find out where you can attend!

Top Solution Authors
Top Kudoed Authors (Last 30 Days)
Users online (2,399)