Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.

Reply
slink9
Frequent Visitor

Duplicate PivotChart Functionality

Hi,

I have a solution that was developed using Access 2010. The data is entered using a form or it can be imported from an Excel sheet. The user will then pull up Chart view and can drag headers from the top of the window onto the desired axis on the pivot table to display the chart. Then the header can be deleted from the axis and another inserted. This provides a truly user-friendly interface for a non-technical person.

Now we are upgrading to Access 2016 and there is no Chart view. From what I can tell, the pivot chart was removed from Access in the 2013 version. What can I do to retain this functionality in Access 2016?

When researching this, I found an Excel-datasource suggestion, but the user is not comfortable with this solution. I also found PowerBI desktop, but seem unable to get the desired ease-of-use from this software. Has anyone found a workable solution for PivotCharts utilizing an Access datasource?

1 ACCEPTED SOLUTION

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.

View solution in original post

19 REPLIES 19

Helpful resources

Announcements
Microsoft Fabric Learn Together

Microsoft Fabric Learn Together

Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City

PBI_APRIL_CAROUSEL1

Power BI Monthly Update - April 2024

Check out the April 2024 Power BI update to learn about new features.

April Fabric Community Update

Fabric Community Update - April 2024

Find out what's new and trending in the Fabric Community.