Combining multiple tables that are based on measures

[TSI]

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 EvolutionWould anyone be able to help? Thank you!

[AlB]

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]

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:

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

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!

[v-frfei-msft]

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.

[TSI]

Hi @v-frfei-msft ,

Please find sample data here.

Appreciate any insights you have.  

[v-frfei-msft · Accepted solution]

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])

[TSI]

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: