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?

Geradav's avatar
Geradav
Icon for Responsive Resident rankResponsive Resident
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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
    Geradav
    Icon for Responsive Resident rankResponsive Resident
    7 years ago

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

    And providing some sample data might help too