Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
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:
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.
Solved! Go to Solution.
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
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
User | Count |
---|---|
139 | |
113 | |
103 | |
73 | |
63 |
User | Count |
---|---|
136 | |
125 | |
107 | |
70 | |
61 |