cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
slink9 Frequent Visitor
Frequent Visitor

Re: Duplicate PivotChart Functionality

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.

dkay84_PowerBI New Contributor
New Contributor

Re: Duplicate PivotChart Functionality

The matrix visual combined with slicers is the way to go here.

As for sample data, you can anonymize it.
GarryFarrell Regular Visitor
Regular Visitor

Re: Duplicate PivotChart Functionality

Hi,

 

Take a look at these samples I made. Download both files and place them in the same folder. Open the pbix file in Power BI Desktop. Click on the categories in the charts to see what the brushing does. (like cross filtering.)

Northwind.pbix

Northwind2016.accdb

 

Regards,

Garry

slink9 Frequent Visitor
Frequent Visitor

Re: Duplicate PivotChart Functionality

Thanks Garry,

 

That looks like it will work quite well for what the user wants, except that I still don't see a way to compare, let's say, three years of data on a line chart. He would like to see the 2014 year compared to 2015 and 2016. Next level in would be monthly comparison, weekly comparison, etc. There are many years of data in one dataset so breaking thsi dataset into individual years is where I am coming up short.

 

Since this was typed but never posted, I have a comment to post for review/suggestion. It sounds like I can't divide a single dataset into multiples by year within PowerBI. If I create three queries by year with Access and use those three queries as the data sources, will that show three lines that I can compare as explained above.

dkay84_PowerBI New Contributor
New Contributor

Re: Duplicate PivotChart Functionality

Are you saying you want three lines on the same chart with month as the X axis, with each line for each of the years?  You could very easily put month name on the axis and your data as the values and then add a slicer for Year, but that will only show one year at a time.  Otherwise, you will have to do some more "advanced" ETL to get the data in a format where your have different fields for different years.

slink9 Frequent Visitor
Frequent Visitor

Re: Duplicate PivotChart Functionality

Comparing three years of data on one line chart would seem to be a basic expectation of PowerBI since it is an attempt to replace the Pivot Chart that was removed from Access. Why this is so hard to accomplish is beyond me. I have created three queries with a year filter in Access. Then I placed the data from all three queries on a line chart. I see three lines, but only one year is broken out. The other two years display a solid line with the total for the year.

dkay84_PowerBI New Contributor
New Contributor

Re: Duplicate PivotChart Functionality

The "difficulty" is a function of your data structure and data model, not the tool.  To have three separate lines, you need three separate fields in your table.  So you would have column 1 is a day of year or Month+Day key, and then column 2 would be 2014 values, column 3 would be 2015 values, etc for those days.  If your values data is in one continuous field and not broken out by year, you will need to do that.

dkay84_PowerBI New Contributor
New Contributor

Re: Duplicate PivotChart Functionality

I will take a look at the exact steps later but you should be able to do this easily if you first add a calculated column (in query editor) to extract year, transform the date to only be month, week, or day of year etc, then perform a pivot to get distinct years as column headers with subsequent values for each month, week or day of year.

slink9 Frequent Visitor
Frequent Visitor

Re: Duplicate PivotChart Functionality

I am still quite baffled. It finally made sense that I should be able to create three queries, in this case for the 2012/2013/2014 data. I should then create expressions with Month([Date]) and Day([Date]). Then I thought I could add these three queries as data sources and place the three fields (either day or month) on the line chart, but that didn't work either. Creating calculated columns on an existing query seems convoluted ... awaiting exact steps.

dkay84_PowerBI New Contributor
New Contributor

Re: 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.

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Summit North America

Power Platform Summit North America

Register by September 5 to save $200

PBI Community Highlights

PBI Community Highlights

Check out what's new in the Power BI Community!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Kudoed Authors
Users Online
Currently online: 43 members 1,155 guests
Please welcome our newest community members: