cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Jon8838 Frequent Visitor
Frequent Visitor

Comparing Individual Performance against Averages Multiple Columns

Hello All!

 

I've got data that is set out as:

Sales PersonMonthSalesExpenses
Person 1Jan-161000100
Person 1Feb-162000200
Person 1Mar-163000300
Person 2Jan-161000100
Person 2Feb-162000200
Person 2Mar-163000300
Person 3Jan-161000100
Person 3Feb-162000200
Person 3Mar-163000300

 

and I am trying to create a clustered column chart that shows an individual's Sales, Expenses and Profit/Loss goverened by a Slicer choosing Month and a Slicer choosing Sales Person compared against an average across all Sales Persons for the selected month. Similar to this:

 

Example.png

 

Through unpivoting and calculating, I can get the individual columns to be created but I'm struggling with getting the Average columns to be created and to be added to a column chart, clustered as shown in the photo.

 

Thanks for any help you can provide!

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-qiuyu-msft
Moderator

Re: Comparing Individual Performance against Averages Multiple Columns

Hi @Jon8838,

 

In your scenario, you can create a measure like this to calculate average for all persons on selected month like below:

 

Average values = CALCULATE( SUM(Sheet4[Value]), ALLEXCEPT( Sheet4, Sheet4[Month], Sheet4[Attribute] ) ) 
/
CALCULATE( DISTINCTCOUNT( Sheet4[Sales Person] ), ALLEXCEPT( Sheet4, Sheet4[Month], Sheet4[Attribute] ) ) 

 

z2.PNGz3.PNG

 

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
1 REPLY 1
Moderator v-qiuyu-msft
Moderator

Re: Comparing Individual Performance against Averages Multiple Columns

Hi @Jon8838,

 

In your scenario, you can create a measure like this to calculate average for all persons on selected month like below:

 

Average values = CALCULATE( SUM(Sheet4[Value]), ALLEXCEPT( Sheet4, Sheet4[Month], Sheet4[Attribute] ) ) 
/
CALCULATE( DISTINCTCOUNT( Sheet4[Sales Person] ), ALLEXCEPT( Sheet4, Sheet4[Month], Sheet4[Attribute] ) ) 

 

z2.PNGz3.PNG

 

 

If you have any question, please feel free to ask.

 

Best Regards,
Qiuyun Yu

Community Support Team _ Qiuyun Yu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.