Power BI is turning 10! Tune in for a special live episode on July 24 with behind-the-scenes stories, product evolution highlights, and a sneak peek at what’s in store for the future.
Save the dateEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
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.
Solved! Go to 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
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
Thank you! That worked
Thank you @kurt_peters for posting this question
to make it more clear, would you please provide the expected results in a table format?
User | Count |
---|---|
22 | |
11 | |
8 | |
6 | |
6 |
User | Count |
---|---|
25 | |
13 | |
11 | |
9 | |
6 |