cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper I
Helper I

Visual to compare monthly performance of different sales people

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.

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Super User I
Super User I

Re: Visual to compare monthly performance of different sales people

@barcamessi 

 

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:

combined chart.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

4 REPLIES 4
Highlighted
Super User I
Super User I

Re: Visual to compare monthly performance of different sales people

@barcamessi 

 

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:compare sales rep.JPG

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






Highlighted
Super User IV
Super User IV

Re: Visual to compare monthly performance of different sales people

@barcamessi , 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.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Helper I
Helper I

Re: Visual to compare monthly performance of different sales people

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

barcamessi_1-1593708878975.png

 

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. 

 

Highlighted
Super User I
Super User I

Re: Visual to compare monthly performance of different sales people

@barcamessi 

 

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:

combined chart.JPG

 

 





Did I answer your question? Mark my post as a solution!
In doing so, you are also helping me. Thank you!

Proud to be a Super User!
Paul on Linkedin.






View solution in original post

Helpful resources

Announcements
August 2020 Community Challenge: Can You Solve These?

August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

Community Blog

Community Blog

Visit our Community Blog for articles, guides, and information created by fellow community members.

Upcoming Events

Upcoming Events

Wondering what events you could join or have an event to promote yourself? Check out our Upcoming Events.

Community Summit Australia – Join Online!

Community Summit Australia – Join Online!

Be a part of the leading Microsoft Business Applications digital event, curated for the APAC community.

Top Solution Authors
Top Kudoed Authors