Forum Discussion
Helper IICombine values from different table visual to a single table visual
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.
Hi swaroopkumarmg ,
There isn't an exact equivalent function in Power BI.
Refer:Solved: Getpivotdata equivalent - Microsoft Power BI Community
Best Regards
Lucien
5 Replies
smpa01Community Champion
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
- v-luwang-msft
Community Support
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 RegardsLucien
- 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
Hi swaroopkumarmg ,
There isn't an exact equivalent function in Power BI.
Refer:Solved: Getpivotdata equivalent - Microsoft Power BI Community
Best Regards
Lucien
- 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