Help with SUMX

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