Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

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,               ...
Need Help
  • Geradav's avatar
    Geradav
    7 years ago

    Hi Anonymous 

     

    Here is the DAX statement you can use

     

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

    Does that work for you?

xuexi1890's avatar
xuexi1890
Icon for Helper I rankHelper I
7 years ago
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]))
Anonymous's avatar
Anonymous
Not applicable
7 years ago

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