Forum Discussion
Dax table SUMX Distinctcount
Hi!
I got a database log that collect info about my picking rounds.
I´ve tried to create a new table that sums the info about the rounds but my DAX knowledge is unfortunatley not so good.
| Reg-date | User | Pick-Code | Queue | Round | Weight | Volume |
| 2019-01-28 13:57:44 | 11111 | 3 | 10 | 55555 | 0,12 | 1,15 |
| 2019-01-28 13:55:30 | 11111 | 3 | 10 | 55555 | 1,00 | 8,21 |
| 2019-01-28 13:54:11 | 11111 | 3 | 10 | 55555 | 0,61 | 3,17 |
| 2019-01-28 13:53:38 | 11111 | 3 | 10 | 55555 | 0,22 | 3,31 |
| 2019-01-28 13:52:32 | 11111 | 3 | 10 | 55555 | 0,78 | 4,80 |
| 2019-01-28 13:51:38 | 11111 | 3 | 10 | 55555 | 1,56 | 9,60 |
| 2019-01-28 13:50:16 | 11111 | 3 | 10 | 55555 | 1,56 | 9,60 |
| 2019-01-28 14:00:02 | 22222 | 3 | 20 | 66666 | 1,45 | 15,14 |
| 2019-01-28 13:59:20 | 22222 | 3 | 20 | 66666 | 0,62 | 2,09 |
| 2019-01-28 13:58:42 | 22222 | 3 | 20 | 66666 | 0,84 | 5,25 |
| 2019-01-28 13:57:12 | 22222 | 3 | 20 | 66666 | 0,85 | 4,59 |
| 2019-01-28 13:56:20 | 22222 | 3 | 20 | 66666 | 1,89 | 5,87 |
| 2019-01-28 13:55:19 | 22222 | 3 | 20 | 66666 | 0,83 | 2,06 |
| 2019-01-28 13:53:49 | 22222 | 3 | 20 | 66666 | 1,86 | 18,58 |
| 2019-01-28 13:51:48 | 22222 | 3 | 20 | 66666 | 0,55 | 1,98 |
| 2019-01-28 13:51:16 | 22222 | 3 | 20 | 66666 | 0,03 | 0,18 |
| 2019-01-28 13:50:44 | 22222 | 3 | 20 | 66666 | 0,63 | 2,28 |
What i want to do is to create a table that looks like this. The round is distinct and a want to sum up weight and volume on my rounds and i want to show how many of "Pick codes" that is on the round.
From that table i will be able to create measures :)
| Date | Round | User | Nr of codes | Queue | Weight | Volume |
| 2019-01-28 | 55555 | 11111 | 7 | 10 | 5,84 | 39,83 |
| 2019-01-28 | 66666 | 22222 | 10 | 20 | 9,54 | 58,01 |
Does anyone have a good DAX forumla idea?
I´ve tried SUMX and DISTINCT
Anonymous something like this and you can tweak as per your need:
Table 2 = SUMMARIZECOLUMNS( Table1[Reg-date].[Date], Table1[Round], Table1[User], Table1[Queue], "Nr of codes", COUNT( Table1[Pick-Code] ), "Weight", SUM(Table1[Weight]), "Volume", SUM(Table1[Volume] ) )
3 Replies
- parry2k
Super User
Anonymous you can create new table using summarizecolumns DAX function
- parry2k
Anonymous something like this and you can tweak as per your need:
Table 2 = SUMMARIZECOLUMNS( Table1[Reg-date].[Date], Table1[Round], Table1[User], Table1[Queue], "Nr of codes", COUNT( Table1[Pick-Code] ), "Weight", SUM(Table1[Weight]), "Volume", SUM(Table1[Volume] ) )