Forum Discussion
Help needed with slicer & Matrix - Filter needed for different column (Picture included)
Hi all,
I am currently trying to put together a sample dashboard that showcases monthly as well as weekly performance of employees.
I now have a matrix/pivot table in PowerBI that shows a geographical breakdown of the region that the employee belongs to as well as average monthly (March) sales amount generated by the individual employees.
Using DAX formula I was able to come up with percentile ranking of each employee within their respective regional group, what I am trying to accomplish is that when I filter for employees 25% or below (refer to column C of the diagram), and then moving across the right hand side of the page, the user using perhaps a second slicer is able to click on the 1st/2nd/3rd/4th week of April in the slicer, and then track their sales activity as we progress through the new month (April) and also allow for comparsion with March average.
I can't seem to do this because in the slicer if I select say the first week of April, it becomes 5x selection in the slicer (4x March weeks) + 1 April week, it distorts the figures seen in column B.
I guess to put it simply I need a secondary matrix/table linked to the original matrix to show performance throughout the weeks of April, is there any way to do this using DAX?
Would really appreciate any help
Thanks!
2 Replies
yuj178 , In date table, You need to create week, Month, Week of month etc
Month Year = FORMAT([Date],"mmm-yyyy")
Month Year sort = FORMAT([Date],"yyyymm")Month Year = FORMAT([Date],"mmm")
Month Year sort = month([DAte])Month Rank = RANKX(ALL('Date'),'Date'[Month Year Sort],,DESC,Dense)
Week Start date = 'Date'[Date]+-1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date]+ 7-1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date] & " to "& [Week End date]
Weekday = WEEKDAY([Date],2)
WeekDay Name = FORMAT([Date],"ddd")month week No = quotient(datediff([eomonth([date],-1)+1,[date],day),7)+1
or
Start Month = eomonth([Date],-1)+1
Month Week no= QUOTIENT(DATEDIFF(Minx(FILTER('Date',[Start Month]=EARLIER([Start Month])),'Date'[Start of Week]),[Date],DAY),7)+1
refer
Power BI — Week on Week and WTD
https://medium.com/@amitchandak.1978/power-bi-wtd-questions-time-intelligence-4-5-98c30fab69d3
https://community.powerbi.com/t5/Community-Blog/Week-Is-Not-So-Weak-WTD-Last-WTD-and-This-Week-vs-Last-Week/ba-p/1051123
https://www.youtube.com/watch?v=pnAesWxYgJ8Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKAHi amitchandak
I am connected to sql server as import and I can't make any changes to the table structure, any other ways to do it using DAX?
Maybe even linking two matrix?
