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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
dodgun
Frequent Visitor

Pivoting to Power BI from Tableau - Question regarding tables

Currently use Tableau, but want to pivot to power BI for more features. I am currently going through tutorials and the documentation to learn power bi, but would appreciate it if someone could tell me if power BI can achieve a basic table below, and perhaps some tips on how to achieve this:

 

example.png

example2.png

 

In playing around with BI I noticed these 2 issues:

- Week numbers. I found a way to show the dates below the years by duplicating the date field, and grouping them. But I still cannot display the week numbers.

- In the Matrix view having 2 rows (Category and Subcategory) it can only show one at a time, or the subcategory under each category. I can fix this by disabling Stepped Layout but then the years are no longer on a hierarchy above the weeks.

- I am a bit confused how best to achieve the year over year calculation in power BI.

 

Thank you.

1 ACCEPTED SOLUTION
danno
Resolver V
Resolver V

I would definitely include a Calendar table, a simple way to do that is to Create a Table using 'New Table' in the Modeling menu.  Calendar = CALENDAR(Min([Date Column]), Max([Data Column]))

If you point this at your date column you will get a list of all dates in the range of your data. 

Add a Week column where Week = "Week " & WEEKNUM(Data[Date])

Add the Week column to your Matrix visual and filter to the week range you want by using a Slicer.

If you want the column headings exactly as you show then you will need the solution here: https://community.powerbi.com/t5/Desktop/Pivot-Table-Matrix-Can-I-use-values-as-a-column-heading/m-p... - by default power bi will split the measures based on categories, but you will need a category for each of your measures.  - However, In my opinion it is better for comparison of the values year on year if the measures are next to each other in the matrix

View solution in original post

1 REPLY 1
danno
Resolver V
Resolver V

I would definitely include a Calendar table, a simple way to do that is to Create a Table using 'New Table' in the Modeling menu.  Calendar = CALENDAR(Min([Date Column]), Max([Data Column]))

If you point this at your date column you will get a list of all dates in the range of your data. 

Add a Week column where Week = "Week " & WEEKNUM(Data[Date])

Add the Week column to your Matrix visual and filter to the week range you want by using a Slicer.

If you want the column headings exactly as you show then you will need the solution here: https://community.powerbi.com/t5/Desktop/Pivot-Table-Matrix-Can-I-use-values-as-a-column-heading/m-p... - by default power bi will split the measures based on categories, but you will need a category for each of your measures.  - However, In my opinion it is better for comparison of the values year on year if the measures are next to each other in the matrix

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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