Forum Discussion
IF statement for each row
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...
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.
8 Replies
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
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.
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
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.
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...So I was able to solve the first problem, it was a formating problem. But I just can't get around the last problem...
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!
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
