cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
WaqarArshad Regular Visitor
Regular Visitor

Calculate Averages by sample number

Hello, 

 

I have a dataset where i would like to caclualte the average per sample number. 

Below is how the data is displayed in the report

 

Chemical,   sample number, Value 

A,               11111,                     5

B,               11111,                     6 

C,               11111,                     4

A,               11112,                     8 

B,               11112,                     9 

C,               11112,                     10

 

I would like this to become 

 

Chemical,    sample number,  Value,        Average 

A,               11111,                         5,                   15

B,               11111,                         6,                   15

C,               11111,                         4,                   15

A,               11112,                         9,                   27

B,               11112,                         8,                   27

C,               11112,                        10,                  27

 

Please note: I would like the average to be displayed at each row in my report. I have used averagex function and it works when my table is only displaying a unique list of sample numbers but as soon as i introduce the chemical parameter, the sample numbers are no longer aggrigated and the average function retunrns the value itself as average. 

 

Any help would be appreciated. 

 

Thank you!

1 ACCEPTED SOLUTION

Accepted Solutions
Geradav Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Hi @WaqarArshad 

 

Here is the DAX statement you can use

 

AvgSampleNumber =
CALCULATE (
    AVERAGE ( DataSetTabl[Value] ),
    ALLEXCEPT ( DataSetTabl, DataSetTabl[Sample number] )
)

Does that work for you?

View solution in original post

8 REPLIES 8
xuexi1890 Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Hi,
 
i don't think you are presenting average, but a subtotal by sample number, below DAX might work for you.
 
Grand Total = CALCULATE(sum(Table1[Value]),all(Table1[Chemical]))
WaqarArshad Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Hi,

Thank you for your reply. You're right! I forgot to divide the numbers. Below is the edited post. Thanks

 

Before 

 

Chemical,   sample number, Value 

A,               11111,                     5

B,               11111,                     6 

C,               11111,                     4

A,               11112,                     8 

B,               11112,                     9 

C,               11112,                     10

 

I would like this to become 

 

Chemical,    sample number,  Value,        Average 

A,               11111,                         5,                   5

B,               11111,                         6,                   5

C,               11111,                         4,                   5

A,               11112,                         9,                   9

B,               11112,                         8,                   9

C,               11112,                        10,                  9

 

 

Geradav Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Hi @WaqarArshad 

 

Here is the DAX statement you can use

 

AvgSampleNumber =
CALCULATE (
    AVERAGE ( DataSetTabl[Value] ),
    ALLEXCEPT ( DataSetTabl, DataSetTabl[Sample number] )
)

Does that work for you?

View solution in original post

WaqarArshad Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

That worked!!  Thank you for your help. 

I have another issue now. Sometimes the Value can be blank and if so, the formula ignores that e.g.

 

1010101,  13

1010101,

1010101, 85 

1010101,

1010101, 31 

 

The formula is returning 43 but i would like it to return 25.8

 

 

xuexi1890 Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Hi,

 

i think you either edit your database in the query when blank=0, then the averagex() should work.

OR

you can count of sample number to avoid the values being blank.

Geradav Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

@WaqarArshad 

 

In Power Query use the Replace Value function

Annotation 2019-08-05 131337.jpg

WaqarArshad Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Thank you all for your replies. 

 

This did do the trick for me @Geradav 

AvgSampleNumber =
CALCULATE (
    AVERAGE ( DataSetTabl[Value] ),
    ALLEXCEPT ( DataSetTabl, DataSetTabl[Sample number] )
)

 

I have another issue now where i would like to calculate the average between two dates e.g. 

Currently its calculating the average over all sample numbers while ignoring the sample date. 

I would like to calculate and display the average between

2016-17

2017-18

2018-19

 

If sample number date is 01/05/18, i would like the average column to display the average by calculating over all samples from 2018 only. 

 

Any help would be appreciated!

 

 

Geradav Regular Visitor
Regular Visitor

Re: Calculate Averages by sample number

Maybe you'll need to open a separate post for that.

And providing some sample data might help too

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 284 members 3,033 guests
Please welcome our newest community members: