Combine values from different table visual to a single table visual

[swaroopkumarmg]

Hello there. 

Problem: i have a single data set - data is in a single excel sheet, but I have to create different pivots from the same data set. 

once I have the pivots ready I need to take the values from those pivots and put them in a combined table. 

Example: 

Table1
Department	Male
A	3
C	6
E	4

Table 2
Department	Female
B	5
D	4
E	6

Combined Table
Department	Male	Female
A	3
B		5
C	6
D		4
E	4	6

here, Table 1 and Table 2 are different pivots with different type of filtering and I have to copy past the values into a separate table to make it presentable. i have to do this every time this report is pulled. 

any way this can be setup in power BI without creating new tables, as I have more than 6 pivots that will be combined this way and I don't want to create new tables. rather I would want to create table/ matrix visual and then pull data from them, similar to the way it's done on excel. 

[v-luwang-msft]

Hi @swaroopkumarmg ,

Has your problem been solved, if so, please consider Accept a correct reply as the solution or share your own solution to help others find it.

Best Regards
Lucien

[v-luwang-msft]

Hi @swaroopkumarmg ,

Try to create a measure like below:

d = IF(MAX(TableA[Department])<>BLANK(),MAX(TableA[Department]),MAX(TableB[Department]))

Final output:(without creating a new table)

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

Best Regards

Lucien

[swaroopkumarmg]

Hi Lucien,

thanks for giving this a try.

What I'm actually looking at is this.

here Male & Female tables are matrix tables and the other one is a normal table visual. 

in the actual report, I'm working on, I have 10+ pivots in my excel file, I do not want to create 10+ tables in Power BI. Rather retrieve data from only one data table (sheet1)

is there a way for the Normal table to reference columns from both the Matrix tables? like how GETPIVOTDATA can be used in excel?  the reason is each of those pivots has a different kind of filtering.

[v-luwang-msft · Accepted solution]

Hi  @swaroopkumarmg ,

There isn't an exact equivalent function in Power BI.

Refer:Solved: Getpivotdata equivalent - Microsoft Power BI Community

Best Regards

Lucien

[smpa01]

@swaroopkumarmg  once you have all the tables, you need to build a table with only Axis and then you can manipulate existsing measures to give tyou what yoyu need. Example attached