Hi...I am looking both for a technical answer to this specific problem, but also thinking I need to level up to probably using functionality yet to be explored (by me) such as learning everything about Data Models.rnMy team has been creating reports to read out to Executives for my company using probably 50 vlookups per report. I was lured to the team because I was told we'd be doing cool things with PowerBI and Tableau and automation (oh, and because they can't retain anyone in this position). After seeing reality, I believe that is years away so looking for an interim solution with Excel.rnI joined 2 tables, and am pulling them into a pivot, it shows the same total for all row fields. Something is not right with the join. As part of testing, I created 2 very small tables and joined them to the same result (attached). rnI would be so grateful to know if I'm doing something wrong, or if this really can't be done, before defeatedly returning to vlookups. But secondly, should I be using more advanced functionality to do this? Always happy to learn if I have some direction.
Thank you in advance. ~Lisa
Hi Lisa,
Welcome to our forum!
The file didn't get attached. You need to click 'start upload' after selecting your file, then wait for the grey check mark beside the file size before clicking 'submit reply'.
That said, it sounds like you've joined two fact tables together and what you need is a dimension table to join them to, then you use the fields from the dimension table in your row/column labels and the fields from your fact tables in the values area.
Hope that points you in the right direction. If you want to learn Power Pivot and DAX, please consider my Power Pivot and DAX course.
Mynda
Thanks, Mynda. Will check out your course! Was hoping to join tables via relationships as an intermediate step until I have time to learn new functionality. I think the file was too large--will try to attach again. I believe I have one fact table (w/ repeating key field) and one dimension table (no repeating key field), and then I tried to display the pivot table with the dimension field
in rows and fact in values, but it displays grand total (same #) for each dimension row variable.
Hi Lisa,
The VacayLookup quasi dimension table is filtering the SampleRoster fact table, therefore the Year field in the PivotTable must come from the dimension table, not the fact table. In reality, what you have is two fact tables. It just so happens that the VacayLookup table has a unique list of years which enables it to behave like a dimension table.
If your PivotTable only needs the year and vacation days fields, then you can build that from the VacayLookup table fields. If you also want fields from the SampleRoster table in the PivotTable, then you really should add a dimension table for the year field and relate your tables to that. You'd then use the year field from the dimension table in the PivotTable row labels.
Mynda
Thanks much, Mynda! Very much appreciated.
Follow-up question: Do you or other instructors/admins/moderators do consulting as well as offer courses?
Cheers,
Lisa
Hi Lisa,
I don't have time for consulting, but I can refer you to a consultant. If you reach out via email website at MyOnlineTrainingHub.com and let me know what you want help with, I can recommend someone.
Mynda
