cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
GilesWalker Established Member
Established Member

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:

 

TQI.PNG

 

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

1 ACCEPTED SOLUTION

Accepted Solutions
v-haibl-msft Super Contributor
Super Contributor

Re: 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.

Help with SUMX_1.jpg

 

  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.
    Help with SUMX_2.jpg
  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 )
    Help with SUMX_3.jpg
  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] ) )
    )
    Help with SUMX_4.jpg

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

     

    Best Regards,

    Herbert

2 REPLIES 2
v-haibl-msft Super Contributor
Super Contributor

Re: 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.

Help with SUMX_1.jpg

 

  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.
    Help with SUMX_2.jpg
  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 )
    Help with SUMX_3.jpg
  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] ) )
    )
    Help with SUMX_4.jpg

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

     

    Best Regards,

    Herbert

GilesWalker Established Member
Established Member

Re: Help with SUMX

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

Helpful resources

Announcements
Virtual Launch Event

Microsoft Business Applications October Virtual Launch Event

Join us for an in-depth look at the new innovations across Dynamics 365 and the Microsoft Power Platform.

Power BI Helps Homeless and Trouble Youth

Power BI Helps Homeless and Trouble Youth

We spoke with Power BI Super User, Greg Deckler, about his charity work

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Users Online
Currently online: 6 members 1,131 guests
Please welcome our newest community members: