Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
vdog01
Frequent Visitor

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
2018-12-22 10_20_17-adventureworks (8) - Power BI Desktop.png
 
and with filtering on, the average changes
 
2018-12-22 10_21_16-adventureworks (8) - Power BI Desktop.png
 
 
 
 
 
 
 
 
 
 
 
 
 
 
 
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
 
2018-12-22 10_25_36-adventureworks (8) - Power BI Desktop.png
 
with filtering
 
2018-12-22 10_26_15-adventureworks (8) - Power BI Desktop.png
 
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?
 
 
 
1 ACCEPTED SOLUTION

Hi @vdog01

 

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

 

Regards,

Cherie

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

View solution in original post

3 REPLIES 3
v-cherch-msft
Employee
Employee

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

 

 

 

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

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.

 

 

Hi @vdog01

 

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

 

Regards,

Cherie

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

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.