Visual to compare monthly performance of different sales people

[Anonymous]

Hi, new to PowerBI, hopefully this question will make sense.

I've been asked to create a visual that allows our sales VPs to see how their team members are performing monthly, and then to contrast their performance against the rest of the team. My model contains a table that has the date the Opportunity is created with the Amount, sales rep ID. A separate table contains the sales rep ID, name, and the region that they belong to.

I'm using a Line Chart right now (not married to it) but that just shows a single line for the amount. What I would like is to have a multi select slicer appear on the page, that allows the user to select more than 1 sales rep, and when another rep is added, have it show as a separate line on the same visual.

Eventually to take it even further, what they would like is to see how a given rep performs against the rest of the team in the Region. 

Hopefully that's enough to go with! Thank you all in advance.

[amitchandak]

@Anonymous , You should have region as axis and sales person as a legend . Based on your selection of sales person, no of lines can increase. You can also have a clustered bar chart.

[PaulDBrown]

@Anonymous 

If you add the Sales Rep to your legend in the chart you will get a line for each rep you select in the slicer.

To make the slicer multi- select, do so in the format pane under selection controls for the visual. 

The caveat is that if you follow ths method, you can only have one measure in the values bucket. If you need to compare one Sales ID to the rest of sales reps, you wil need to do so in a seperate visual. To calculate the values for the whole team, use ALL in the Filter expression of a CALCULATE function. In my example (visual on the right) I'm comparing actuals of Channel B to actuals of all Channels. The sum of all channels is:

Actuals of ALL Channels = CALCULATE(SUM('Fact Table'[Actuals]), ALL('Channel Dim'[Channel]))

Here is a simple example:

[Anonymous]

Thanks! That helps a lot. Couple of follow up questions, I ended up switching to a clustered column chart instead.

To get around the month gaps where there was no activity, I created a measure instead of using the Amount field directly, used the following:

OppAmount = IF(ISBLANK(CALCULATE(SUM(Opportunity[Amount]))),0,CALCULATE(SUM(Opportunity[Amount]))) but this is showing months that we haven't even reached yet, is there a way that I can modify this Measure so that it returns blanks if the Month is in the future? Using MONTH(TODAY()) somehow?

I'm struggling with the second visual on the same page, I think they're looking for a visual to contrast Sales Person A performance against the Region average that Sales Person A belongs to. Could I use a line and clustered column chart, where:

Column Series is Name

Column Values is the Opportunity Amount

Line Values is a measure, that calculates the Average Opportunity Amount for the region? Region is one of the slicers that I have visible. 

[PaulDBrown · Accepted solution]

@Anonymous 

To remove "future months", you can ceratinly use MONTH(TODAY()). Please create a "Calendar" table! and use it in your filters, slicers, visuals.... 
Something along the lines of:

OppAmount = IF(Calendar[month] > MONTH(TODAY()),BLANK(),CALCULATE(SUM(Opportunity[Amount]))) 

You can use a combined clustered/line chart if you wish. Here is an example where the columns refer to the values selected in the slicer (actuals and target for selected "B") and the line is the Actuals for ALL channels: