Forum Discussion
Duplicate PivotChart Functionality
It is impossible to give "exact steps" without sample data.
Can you provide a screenshot of your underlying data structure? Below is the structure you will need to create to get three lines in a chart that represent individual calendar years:
Month+Date Key | 2012 data | 2013 data | 2014 data | Date Axis Sort Order
Jan 1 x x x 1
Jan 2 x x x 2
Jan 3 x x x 3
...
etc.
Then you would create a line chart and put the Month+Date Key as the axis. Sort the Month+Date Key field by the Date Axis Sort Order column (under the Modeling tab I believe). Then add the 2012, 2013, and 2014 fields to your Values area and they will share the common axis but each represent a separate year.
If you want to only display 1 year at a time and have a slicer for year, then the table structure needs to be different, or you have to do some more complex DAX which is probably not a good choice given your challenges so far.
I was thinking that it would, but I have not been impressed so far. Do you have some guidance on accomplishing this feat? It doesn't matter whether the solution requires back end programming or can be accomplished directly through UI setup, as long as the result is drag and drop (or check/uncheck) for the end user. I have come up completely empty on trying to research this.
Microsoft EmployeeMy recommendation is to connect Power BI to Access and use Power BI as your visualization layer. To connect to Access, make sure your PBI version (32x or 64x) matches your version of Access. Additionally, you may be required to download the correct database engine (see here for more info: https://powerbi.microsoft.com/en-us/documentation/powerbi-desktop-access-database-errors/).
Once connected, you will be able to select the data you want to connect to, and perform any transformations you wish (all via a UI). Then, the data gets loaded to a data model, and you can create relationships (just like the UI in Access). Then, all you have to do is select which type of visual and drag and drop the fields you want to the axis, values, etc.
I'm not sure why you are coming up "completely empty" on researching this. If you have a specific example/question about functionality or replicating something you did in Access, please provide a screenshot and sample data.
Thanks DKay for the quick response. I apologize for being short on details. I have a screen shot and will explain the need as I understand it.
Data is entered or imported from an Excel sheet and displayed using an Access query called Variable Query. Then, in Access 2010, the user is able to click on View-PivotChart View. to display the chart shown above. The user can then drag any of the fields at the top to the bottom to add/combine data points in the view. One of the duplication issues seems to be getting multiple lines to display based on one date column. The user can drag down Date by Month and compare three years on separate lines. Then the user can view year-month-day. I get the year-month-day capabilities by using levels in PowerBI, but still can't get the individual years comparison to work.
This is a somewhat non-technical user, which is the reason that he likes the drag-and-drop approach. I can sell him on check/uncheck instead as long as the same general functionality can be provided. Do these details help?
One additional detail is that the user can also filter by clicking on the bottom label after it has been added to the pivot chart.
- dkay84_PowerBI
You can drag fields to the rows, columns, values, filters, etc. And you can but slicers in the chart. I'm not sure what you're describing with regards to getting multiple lines to display based on one date column. But, I can assure you that, based on your screen shot, everything you want to do is doable in Power BI. Power BI is essentially a local instance of SSAS tabular, which is designed exactly for doing this type of pivot table/charting.
If you want to provide some sample data from your excel sheet I (we) can show you how to quickly get up and running, but there is a plethora of material online to get you comfortable with the level of complexity you are describing.
Thanks. Can you provide some of the sites that you reference for guidance on creating pivot charts with PowerBI?
Normally, I would be glad to send you data samples and truly accepting of any time saving help. I hesitate to send company data outside so I will just have to do the research and work all on my own. So far, my attempts at getting a pivot table laid out like this have been quite futile.