Forum Discussion
Comparing Individual Performance against Averages Multiple Columns
Hello All!
I've got data that is set out as:
| Sales Person | Month | Sales | Expenses |
| Person 1 | Jan-16 | 1000 | 100 |
| Person 1 | Feb-16 | 2000 | 200 |
| Person 1 | Mar-16 | 3000 | 300 |
| Person 2 | Jan-16 | 1000 | 100 |
| Person 2 | Feb-16 | 2000 | 200 |
| Person 2 | Mar-16 | 3000 | 300 |
| Person 3 | Jan-16 | 1000 | 100 |
| Person 3 | Feb-16 | 2000 | 200 |
| Person 3 | Mar-16 | 3000 | 300 |
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:
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!
Hi Anonymous,
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] ) )
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu
1 Reply
Hi Anonymous,
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] ) )
If you have any question, please feel free to ask.
Best Regards,
Qiuyun Yu