Dax table SUMX Distinctcount

[Tobjoh]

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

[parry2k]

@Tobjoh you can create new table using summarizecolumns DAX function

[parry2k · Accepted solution]

@Tobjoh 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] ) 
)
    

[Tobjoh]

You solved it 🙂

Thanks!