cancel
Showing results for
Did you mean:
Frequent Visitor

## 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!

1 ACCEPTED SOLUTION
Community Support

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] ) ) ```

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.
Community Support

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] ) ) ```

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.

Announcements

#### 2021 Release Wave 2 Plan

Power Platform release plan for the 2021 release wave 2 describes all new features releasing from October 2021 through March 2022.