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

Switch column chart axis by day, week, month, year with buttons

I have a table with a date column and a key figure value.

PBI table.png

I create a column chart with date on X axis and the key figure in values.

PBI chart.png

 

 

 

 

 

 

The request is to switch the X axis from date (represented by day by default) to weeks, months, years granularity with a set of buttons.

After one hour googling, I have created a calender table with columns date, week, month, year

PBI cal table.png

and 4 charts, each with different column name (from the calender table) in the values field

PBI chart2.png

And finally the trick with 4 charts overlayed and 4 buttons, which toggle each chart with the help of bookmarks and selection pane.

But isn't there a easier way to do this? This looks like overkill.

Thank you

2 REPLIES 2
AllisonKennedy
Super User III
Super User III

@zdenek_moravec 

 

That's amazing! I love buttons. 

 

My approach would be to use Drill down in 1 visual. So put Year Month in Axis, Month underneath, Day under that, etc all in the same visual in the correct order. You can probably still set up bookmarks to make this work with buttons, or just train users to use the built in drill down functionality.

 

UPDATED to add: Use the parallel disconnected arrows for drill down to get the same effect as your separate charts. 

 

The Microsoft documentation doesn't use a great example, but here's the info on drill down so you can see what built in buttons I mean: https://docs.microsoft.com/en-us/power-bi/consumer/end-user-drill

 

Also, I suggest making your date table in Power Query if you can, it will speed up your report: https://excelwithallison.blogspot.com/2020/04/dimdate-what-why-and-how.html


Check out my Tokyo 2020 report with live stats - KUDOS much appreciated



Has this post solved your problem? Please mark it as a solution so that others can find it quickly and to let the community know your problem has been solved. 


If you found this post helpful, please give Kudos. ?


I work as a trainer and consultant for Microsoft 365, specialising in Power BI and Power Query. 


www.excelwithallison.com

Hello Allison

The idea with hierarchy is good, I remember, I tried it, but the disadvantage is, that the hierarchy function groups the same month number, week numbers from all years. So I see month 10 in one bar as sum of months 10.2019 and 10.2020. Therefore I created year-month column additionally to month column to see month 10 in two columns - 10.2019 and 10.2020.

Helpful resources

Announcements
PBI_User Group Leader_768x460.jpg

Manage your user group events

Check out the News & Announcements to learn more.

MBAS on Demand

2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.

Get Ready for Power BI Dev Camp

Microsoft named a Leader in The Forrester Wave

Microsoft received the highest score of any vendor in both the strategy and current offering categories.

Top Solution Authors