Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
kurt_peters
New Member

Sum values between two values

Hello Everyone,

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

kurt_peters_1-1664673610850.png

 

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 

kurt_peters_2-1664674021984.png

 

And I only get sum values that has the max values

kurt_peters_3-1664674104447.png

 

 

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

 

1 ACCEPTED SOLUTION
v-yueyunzh-msft
Community Support
Community Support

Hi, @kurt_peters 

Here are the steps you can follow:

(1)This is my test data:

vyueyunzhmsft_0-1664788549553.png

(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":

vyueyunzhmsft_1-1664788612639.png

(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:

vyueyunzhmsft_2-1664788659720.png

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

 

 

 

 

View solution in original post

3 REPLIES 3
v-yueyunzh-msft
Community Support
Community Support

Hi, @kurt_peters 

Here are the steps you can follow:

(1)This is my test data:

vyueyunzhmsft_0-1664788549553.png

(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":

vyueyunzhmsft_1-1664788612639.png

(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:

vyueyunzhmsft_2-1664788659720.png

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

 

 

 

 

Thank you! That worked

tamerj1
Super User
Super User

Thank you @kurt_peters for posting this question 

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

 

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.