IF statement for each row

[Paritäa]

Hi everyone!

So I have been working on this problem the whole day and tried so many different ways to solve it but it just won't work!

I have a table, each row is a new address which has a value.

Because there are over 3000 rows, I want to summarize these values in ranges. I don't care about the addresses, the focus lies on how big each range is.

The ranges should be in steps of 10.

Here is the Excel-File: https://1drv.ms/x/s!Ao7ZiNWuV2I6gcRksSZgh2sOG-12Zg?e=ZoU265 

Here an example of what I want at the end:

For Row "0-9.9": Sum all Values between 0 to <10

For Row "10-19.9": Sum all Values between 10 to <20

For Row "20-29.9": Sum all Values between 20 to <30

...

For Row "+50": Sum all Values >=50

PS: Is it possible to upload the file? Because I won't keep it in my OneDrive forever and it would be sad for future HelpSeekers, if they can't access it anymore...

[Paritäa]

Because I don't know how to insert the ëxample file, I'll just put the table in here:

Address	Value
A	2.5
B	2.8
C	2.4
D	2.4
E	2.5
F	2.6
G	1.2
H	2.7
I	3
J	2.8
K	1.8
L	5.2
M	3.4
N	3
O	1
P	2.5
Q	8.4
R	1.5
S	14.3
T	15.7
U	1.7
V	6.6
W	12.8
X	22
Y	17.4
Z	10
AA	20.4
AB	80
AC	15
AD	1.7
AE	1.7
AF	50
AG	6
AH	12
AI	1.2
AJ	22
AK	6.7
AL	1
AM	12
AN	21
AO	25.5
AP	49.5
AQ	25.5
AR	50
AS	34.5
AT	80
AU	30
AV	10.2
AW	15
AX	12
AY	4.8
AZ	26.5

[Anonymous · Accepted solution]

Hi @Paritäa ,

According to your description, here are my steps you can follow as a solution.

(1) Enter data.

(2) We can create a calculated column.

Sum of value =
SWITCH(
    [Row name],
    "0-9.9",SUMX(FILTER('Table (2)',[Value]>=0&&[Value]<10),[Value]),
    "10-19.9",SUMX(FILTER('Table (2)',[Value]>=10&&[Value]<20),[Value]),
    "20-29.9",SUMX(FILTER('Table (2)',[Value]>=20&&[Value]<30),[Value]),
    "30-39.9",SUMX(FILTER('Table (2)',[Value]>=30&&[Value]<40),[Value]),
    "40-49.9",SUMX(FILTER('Table (2)',[Value]>=40&&[Value]<50),[Value]),
    "+50",SUMX(FILTER('Table (2)',[Value]>50),[Value]))

(3) Then the result is as follows.

Best Regards,

Neeko Tang

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

[Paritäa]

So I was able to solve the first problem, it was a formating problem. But I just can't get around the last problem...

[Paritäa]

Found the last problem! Forgot to put a sign in the name, so it didn't recognize where to put it in the table.

Thank you so much for your detailed answer!

[Paritäa]

Awesome! Finally some results! I do now face two problems:

1. Two results which are weird, as it produces values with 11 decimal digits:
0-9.9     = 302.49999999999999
20-29.9 = 310.30000000000001
How can it result in these many decimal digits if all my values only have 1 decimal digit?

2. The last range +50, does not produce a result. The cell stays empty, even though I do have two values which are higher than 50...

[ryan_mayu]

@Paritäa 

1. create an index column in PQ

2. use DAX to create a group column

group = if('Table'[Index]<50, int('Table'[Index]/10)+1,6)

3. use DAX to create a column to check if the value is in the scope

Column = 
if('Table'[Value]>=('Table'[group]-1)*10 &&'Table'[Value]<('Table'[group]+1)*10,"Yes")

4. create a measure

Measure = CALCULATE(sum('Table'[Value]),'Table'[Column]="Yes")

pls see the attachment below

[Paritäa]

I'd also like to try your version but I don't even know how to enter Power Query and therefore add index columns. I tried with online tutorials but the all have PQ already open.
My only tab options in the Home tab is:

Sorry, it's German but as you can see, I only have the option to add a "basic" column but there are no options to choose what type of columns to add.

[ryan_mayu]

i think I misunderstood your request. 

my solution may not fit your situation. 

Let's see @Anonymous 's solution 

1. you can change the column type

2. it should display values

pls see the attachment below