Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago
Solved

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...
Need Help
Tutorial Requests
  • v-qiuyu-msft's avatar
    v-qiuyu-msft
    10 years ago

    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

v-qiuyu-msft's avatar
v-qiuyu-msft
Community Support
10 years ago

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