12-17-2017 18:58 PM - last edited 01-07-2020 11:29 AM
Are we going to reach the TARGET and receive our bonuses for 2017?
With the year-end approaching, I want to share with my team on a daily basis, how we are scoring against the target, where we can still push, which customer? which product? The target (and bonuses) are within reach!
This Power BI report uses bookmarks and drillthrough to better organize the content, to move fluently between pages, to switch between visuals (toggle) and to drill down Sales Reps, Customers and Products.
- Click on a team member photo to open his/her Sales Rep Bookmark. Identify negative (red) customers.
- Switch between Map and Bar visuals using the Toggle image
- Drillthrough customer (right click on the bar chart) to open Customer Bookmark
- Identify Product opportunities within customer, switch between Scatter and Matrix visuals using Toggle
- On slicer, select UNITS or PROFIT, select PERIOD to understand long and short term trends.
With TOGGLE you can switch between two (or more) visuals. In my example I switch from matrix to scatter, but could be any visual:
Done. Use Ctrl-Mouse (in PBI Desktop) to switch between both visuals
Hi, sorry for asking what may be a rather simple question (I'm not very experienced with Power BI) but how do you manage the Date Slicer in your team member page? The typical date slicer is only in a slider/list format and not nicer chiclets like this, but even if I use the Chiclet slicer visual it still displays all the individual dates instead. How do you do relative time filtering (YTD, past 30 days etc) in this case?
You'll need to do a little bit of work behind the scenes to do this, including writing a few dax formulas. First up you need to create a new 2-column table:
No Date Option
2 Past 30 Days
3 Past 90 Days
4 .... Keep going with all the time options you want.
Also create a new measure: Selected Timeframe:=min(Table[No]). (Eventually you will show the 'Date Option' on the chiclet slicer, and this measure will harvest your selection. Do not create any connections for this table.
Next up, in your main data table, you will need to write a new dax formula for each of the date options. You should be able to use time intelligence functions to help achieve some of these. E.g.
Sum YTD:=TOTALYTD(sum(Data[Values]), Dates[Dates Column], "31/12/2014") where the last value is your year-end date for any year (I've used UK format which may not be approriate for you).
Finally you need one new measure which utilises the SWITCH function:
Selected Measure:=SWITCH([Selected Timeframe],1,[Sum YTD],2,[Sum 30 Days],3,[Sum 90 Days]).
What this measure will do is change the measure depending on the selected chiclet. E.g. If you press 'Past 30 Days', it create the value 2 in the [Selected Timeframe] measure. That will result in selecting the [Sum 30 Days] part of the [Selected Measure].
Now create the chiclet slicer and put the column 'Date Option' as the values (remembering to Sort By [No] to get the values in the right order).
Hope this helps - but let us know if still confused.
Yes, I used the method described by Mike to create the Metric slicer (to select Units or Profit)
However, for the Period slicer (to select YTD, Last 30-days, etc) I used a different approach.
I created a New Table ('Period Table') which combines desired Periods (“Year To Date”, “Last 30 Days", etc) with dates from Date table, something like this:
Looks easy, but it's not. The 'Period Table' needs to be written in Advanced Query Editor (Home-Edit Query-Advanced Editor) which requires some M' knowledge. Fortunately you can find the code for it as well as a great explanation in Chris Webb's blog here.
Hope it helps
Impressive visualization. Can you please let us know the steps taken for desigining the opening page where when we click on images it is taking to the respective pages