Forum Discussion

Frequent Visitor

7 years ago

Solved

Comparing to Averages when filtering

Hello all

I'm having a problem trying to compare a value to an average value. Here's the scenario, using AdventureWorks as an example

I have a measure

Avg Days to Manufacture =

CALCULATE(AVERAGE('Product'[Days To Manufacture]),ALLSELECTED('Product'))

This works as expected when filters are applied, eg with no filtering it shows the expected average

and with filtering on, the average changes

I'm trying to compare the days to manufacture to the average days to manufacture and have created a calculated column as follows

'Product'[Days To Manufacture] / 'Product'[Avg Days to Manuf]

However, this seems to take the average of the entire result set, ignoring the filters.

So here's the value without filters

with filtering

In the second screen shot i would have expected the value to be 4/4 (or 100%) or 8/4 (or 200%)

What am I doing wrong?

v-cherch-msft
7 years ago
Hi vdog01

Glad to hear it has been solved, please accept the solution to close this thread.

Regards,

Cherie

3 Replies

v-cherch-msft
Microsoft Employee
7 years ago
Hi vdog01

You may create a measure instead of calculated column when the values are changed by filters. You may try below measure.

CF to Avg=SUM('Product'[Days To Manufacture])/'Product'[Avg Days to Manuf]

Maybe it needs some modification for the sum measure. You may refer CALCULATE, ALLxxx functions. Then create a measure for 'sum of Days To Manufacture'.

Calculated columns and Measures in DAX.

If you need further help, please show us the context of table visual or the sample data.

Regards,

Cherie
- vdog01
  Frequent Visitor
  7 years ago
  Hi thanks for the update
  I ended up doing the following (which seems to work
  
  Changed Average to
  
  Average = CALCULATE(AVERAGE(Product[Days To Manufacture]),ALLSELECTED(Product))
  
  then in the CF to Avg I made it
  
  AVERAGEX(Product, Product[Days To Manufacture]) / [Average]
  
  It was the first calculation when it was a column that didn't change depending on my selection.
  - v-cherch-msft
    Microsoft Employee
    7 years ago
    Hi vdog01
    
    Glad to hear it has been solved, please accept the solution to close this thread.
    
    Regards,
    
    Cherie