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?

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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

 

 

Geradav's avatar
Geradav
Icon for Responsive Resident rankResponsive Resident
7 years ago

Anonymous 

 

In Power Query use the Replace Value function