Sum values between two values

[kurt_peters]

Hello Everyone,

I'm trying to sum values between two values, for exemple:

This is a sample of my data.

I would like to pull a bar chart that:
- between 0 and 3 sums values of [qnt] of lines(idx) : 1, 2 and 3.
- between 3 and 4 sums values of [qnt] of lines(idx) : 1, 2, 3, 4, 5 ,6, 7
- between 5 and 6 sums values of [qnt] of lines(idx) : 2, 3, 4, 5 ,6, 7, 8 , 9 and 10
- between 6 and 7 sums values of [qnt] of lines(idx) : 2, 3, 6, 7, 8 , 9 and 10

and so on...

I alweady did a table contaning the max and min of thoes values to make it 

And I only get sum values that has the max values

Hope that i could be clear.
Sorry for any misspelling.

[v-yueyunzh-msft · Accepted solution]

Hi, @kurt_peters 

Here are the steps you can follow:

(1)This is my test data:

(2)We can click "New Table" and enter:

Table 2 = 
var _min=MIN('Table'[min])
var _max=MAX('Table'[max])
var _table=GENERATESERIES(_min,_max,1)
var _crossjoin=CROSSJOIN('Table',_table)
var _output=FILTER(_crossjoin,[Value]<=[max]&&[Value]>=[min])
return
_output

(3)We can create a 'Status' table by "Enter Data":

(4)Then we can create a calculated column in 'Status' Table:

Sum = 
SWITCH([Status],
"0-2" ,   var _t =SUMX( FILTER('Table','Table'[Idx] in   DISTINCT(SELECTCOLUMNS( FILTER('Table 2', 'Table 2'[Value] in {0,1,2}),"Id",[Idx])) ) ,[qnt]) return _t      ,
"3-4", var _t =SUMX( FILTER('Table','Table'[Idx] in   DISTINCT(SELECTCOLUMNS( FILTER('Table 2', 'Table 2'[Value] in {3,4}),"Id",[Idx])) ) ,[qnt]) return _t            ,
"5-6",   var _t =SUMX( FILTER('Table','Table'[Idx] in   DISTINCT(SELECTCOLUMNS( FILTER('Table 2', 'Table 2'[Value] in {5,6}),"Id",[Idx])) ) ,[qnt]) return _t           ,
var _t =SUMX( FILTER('Table','Table'[Idx] in   DISTINCT(SELECTCOLUMNS( FILTER('Table 2', 'Table 2'[Value] in {6,7}),"Id",[Idx])) ) ,[qnt]) return _t 
    )

(5)Then we can meet your need, the result is as follows, we can put the fileds in the visual:

If this method does not meet your needs, you can provide us with your special sample data and the desired output sample data in the form of tables, so that we can better help you solve the problem.

Best Regards,

Aniya Zhang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[kurt_peters]

Thank you! That worked

[tamerj1]

Thank you @kurt_peters for posting this question 

to make it more clear, would you please provide the expected results in a table format?