Forum Discussion

GilesWalker's avatar
GilesWalker
Skilled Sharer
9 years ago
Solved

Help with SUMX

Hi everyone,

 

I have got a large data set which records measurements every 25 centimeters. These data points then need to be summed up to 100 meter intervals and divided by the the count of entries.

 

For example:

 

Between 18.2 (including) and 18.3 (not including) the sum of the measurements equals 642.24 with a count of rows equalling 169, this gives an average reading for this distance of 3.8 (642.24/169).

 

What I am struggling with is how to get a formula to do this across all 100 meter measurement. There is approximately 630 km of track which will be measured.

 

My thoughts are that SUMX should do this I just cant figure out how to get the formula to only calculate from the start XXX.0 to XXX.09, and then XXX.1 to XXX.19 etc.

 

Here is an example of the data:

 

 

Custom location is calculated column with the following formula:

 

=Number.Round([LOCATION],2)

 

Everything else is raw data.

 

Hope someone has an awesome idea.

 

Thanks,

 

Giles

Need Help
  • v-haibl-msft's avatar
    v-haibl-msft
    9 years ago

    GilesWalker

     

    In this scenario, we can add a group column in the table. For example, the rows between 18.20 and 18.29 are belong to group 1, rows between 18.30 and 18.39 are belong to group 2. Then we can calculate the average easily.

    But in this method, we need to make sure each meter location exists. For example, there should be 10 distinct custom locations between 18.20 and 18.29.

     

    I’ll use following simple dataset to explain. I’m not sure which value is measurements, so I add a data column as below.

     

    1. Duplicate above table in Query Editor and only keep the custom location column.
    2. Remove duplicates for this custom location column in the duplicated table (I call it Table2 here).
    3. Add an Index column for Table2. Close and apply Query Editor.
    4. Create relationship between Table1 and Table2 with custom location key.
    5. Create a column with following DAX formula in Table2.
      Group = 
ROUNDUP ( Table2[Index] / 10, 0 )
    6. Create a column with following DAX formula in Table1.
      Group = 
RELATED ( Table2[Group] )
    7. Create a column to calculate the average with 100 meter intervals.
      Average = 
DIVIDE (
    CALCULATE ( SUM ( Table1[Data] ), ALLEXCEPT ( Table1, Table1[Group] ) ),
    CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Group] ) )
)


      I’ve uploaded my .pbix file here for reference.

       

      Best Regards,

      Herbert

2 Replies

  • v-haibl-msft's avatar
    v-haibl-msft
    Microsoft Employee
    9 years ago

    GilesWalker

     

    In this scenario, we can add a group column in the table. For example, the rows between 18.20 and 18.29 are belong to group 1, rows between 18.30 and 18.39 are belong to group 2. Then we can calculate the average easily.

    But in this method, we need to make sure each meter location exists. For example, there should be 10 distinct custom locations between 18.20 and 18.29.

     

    I’ll use following simple dataset to explain. I’m not sure which value is measurements, so I add a data column as below.

     

    1. Duplicate above table in Query Editor and only keep the custom location column.
    2. Remove duplicates for this custom location column in the duplicated table (I call it Table2 here).
    3. Add an Index column for Table2. Close and apply Query Editor.
    4. Create relationship between Table1 and Table2 with custom location key.
    5. Create a column with following DAX formula in Table2.
      Group = 
ROUNDUP ( Table2[Index] / 10, 0 )
    6. Create a column with following DAX formula in Table1.
      Group = 
RELATED ( Table2[Group] )
    7. Create a column to calculate the average with 100 meter intervals.
      Average = 
DIVIDE (
    CALCULATE ( SUM ( Table1[Data] ), ALLEXCEPT ( Table1, Table1[Group] ) ),
    CALCULATE ( COUNTROWS ( Table1 ), ALLEXCEPT ( Table1, Table1[Group] ) )
)


      I’ve uploaded my .pbix file here for reference.

       

      Best Regards,

      Herbert

    • GilesWalker's avatar
      GilesWalker
      Skilled Sharer
      9 years ago

      Hi v-haibl-msft,

       

      I have tested this and it appears to be working perfectly. I will be doing a more detailed testing tomorrow and if is all good will mark this off as completed.

       

      Thanks for the help on this, your solution was clear and easy to follow.

       

      Regards,

       

      Giles