cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
TSI Regular Visitor
Regular Visitor

Combining multiple tables that are based on measures

Hi there!

 

I'm new to Power BI and need help combining 3 tables. They are based on measures, hence UNION doesn't work.

Here's what the tables look like (with different column headings except Projected Year), and how they should be combined:Gender evolution tables.jpgGender EvolutionWould anyone be able to help? Thank you!

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Combining multiple tables that are based on measures

Hi @TSI ,

 

Please check the following steps as below.

 

1. Create a calculated table as below.

 

Table = GENERATESERIES(2019,2021,1)

2. Create measures to get the excepted result.

2019 = 0.415
2020 = [2019]+SELECTEDVALUE(Attrition[Attrition])*SELECTEDVALUE('Women Hired %'[Women Hired %])*[2019]
2021 = [2020]+ SELECTEDVALUE(Attrition[Attrition])*SELECTEDVALUE('Women Hired %'[Women Hired %])*[2020]
Measure = SWITCH(MAX('Table'[Value]),2019,[2019],2020,[2020],2021,[2021])

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

6 REPLIES 6
Super User
Super User

Re: Combining multiple tables that are based on measures

Hi @TSI 

If thye are all measures, you can just:

 - Place [Projected Year] in rows of a matrix visual  and place both  measures [Projected Male %] and [Projected Female %] in values of the matrix visual

You do not show how you built the measures so if the above does not work please show the code for the measures and the data tables you involved in the calculation.

Show your sample data in text-tabular format in addition to (or instead of) the screen captures. That allows people trying to help to readily copy the data and run a quick test, plus it increases the likelihood of your question being answered. Just use 'Copy table' in Power BI and paste it here. Or, ideally, share the pbix (beware of confidential data).

 

 

 

TSI Regular Visitor
Regular Visitor

Re: Combining multiple tables that are based on measures

Hi @AlB ,

Thank you for the reply and tips on posting questions.

I tried using a matrix visual, but ended up with multiple columns, not sure if I did it wrong:Gender evolution tables1.jpg

 

The issue I have is that the Gender % projection is a recursive calculation, simplified below:Gender evolution tables2.jpg

 

I tried to split it into measures to get the projection for 2020 and 2021, and add it back into a single table to get the visualisation. 

Is there an easier way to do this? Excel is the obvious choice but we'd like to link the Gender % projection to our current dashboard. 

 

Thanks for your help!

Community Support Team
Community Support Team

Re: Combining multiple tables that are based on measures

Hi @TSI ,

 

Could you please share your sample data to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.
TSI Regular Visitor
Regular Visitor

Re: Combining multiple tables that are based on measures

Hi @v-frfei-msft ,

 

Please find sample data here.

 

Appreciate any insights you have.  

Community Support Team
Community Support Team

Re: Combining multiple tables that are based on measures

Hi @TSI ,

 

Please check the following steps as below.

 

1. Create a calculated table as below.

 

Table = GENERATESERIES(2019,2021,1)

2. Create measures to get the excepted result.

2019 = 0.415
2020 = [2019]+SELECTEDVALUE(Attrition[Attrition])*SELECTEDVALUE('Women Hired %'[Women Hired %])*[2019]
2021 = [2020]+ SELECTEDVALUE(Attrition[Attrition])*SELECTEDVALUE('Women Hired %'[Women Hired %])*[2020]
Measure = SWITCH(MAX('Table'[Value]),2019,[2019],2020,[2020],2021,[2021])

Capture.PNG

 

Community Support Team _ Frank
If this post helps, then please consider Accept it as the solution to help the others find it more quickly.

View solution in original post

TSI Regular Visitor
Regular Visitor

Re: Combining multiple tables that are based on measures

Hi @v-frfei-msft , this is brilliant!

Thank you so much for the elegant solution. 

First time I've seen SWITCH, definitely looking it up.

Managed to create this line chart by following your steps:


Gender evolution line chart1.jpg

 

 

Helpful resources

Announcements
New Kudos Given Badges Coming

New Kudos Given Badges Coming

We're rolling out new Kudos Given badges. Find out how many Kudos you've given.

Microsoft Implementation for Communities Wins Award

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

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (3,230)