topic Re: calculate number of orders/volume/sales based on different shipping location and order reference nr in DAX Commands and Tips

calculate number of orders/volume/sales based on different shipping location and order reference nr

SebaG — Sun, 16 Jun 2019 15:47:15 GMT

HI All

Hope someone can help with bellow excersice, as I'm struglling with it and no result.

So... orders can be shipped from 2 different warehouse location: Italy or France. Sometimes it is only France, sometimes it is only Italy, but sometimes order is split and deliver from both location.

Need to know number of orders, their volume and value by index: FR for France, IT for Italy, FR&IT for France & Italy.

In bellow sample dataset I have order split: FR=1; IT=1; FR&IT = 2

Thanks in advance.

Date	Order Ref Number	Item	Quantity	Item price	Currency	Warehouse Location
2019.06.05	00000005	a	4	1.5	DOL	Italy
2019.06.05	00000005	b	5	1.65	DOL	Italy
2019.06.05	00000005	c	6	1.32	DOL	Italy
2019.06.05	00000005	d	77	1.6	DOL	Italy
2019.06.05	Big Order	a	103	1.5	EUR	Italy
2019.06.05	Big Order	g	405	1.23	EUR	Italy
2019.06.05	Big Order	h	90	1.22	EUR	Italy
2019.06.05	Big Order	b	96	1.69	EUR	France
2019.06.06	for my dad	a	2	1.37	EUR	France
2019.06.06	for my dad	b	3	1.26	EUR	France
2019.06.06	for my dad	g	4	1.87	EUR	France
2019.06.06	for my dad	d	5	1	EUR	France
2019.06.06	for my dad	l	7	1	EUR	Italy
2019.06.06	for my dad	o	2	1.36	EUR	Italy
2019.06.06	for my dad	p	3	1.59	EUR	Italy
2019.06.06	for my dad	z	1	1.49	DOL	Italy
2019.06.06	12345	b	23	1.84	DOL	France
2019.06.06	12345	l	45	1.22	DOL	France
2019.06.06	12345	d	15	1.36	DOL	France
2019.06.06	12345	p	4	1.15	DOL	France
2019.06.06	12345	z	34	1.91	DOL	France

Re: calculate number of orders/volume/sales based on different shipping location and order reference nr

Stachu — Thu, 20 Jun 2019 04:49:53 GMT

you actually need a column that would say 'Italy & France', otherwise you cannot put it in rows of the visual (measures can be only used as values)

try this code for a calculated column

Warehouse = 
VAR __Order = 'Table'[Order Ref Number]
VAR __OrderAllRows = FILTER(ALL('Table'),'Table'[Order Ref Number]=__Order)
VAR __UniqueWarehouses = SUMMARIZE(__OrderAllRows,'Table'[Warehouse Location])
RETURN
CONCATENATEX(__UniqueWarehouses, 'Table'[Warehouse Location], "&")

Re: calculate number of orders/volume/sales based on different shipping location and order reference nr

SebaG — Sat, 29 Jun 2019 13:29:21 GMT

Thank you Stachu, that helped a lot. Job done thanks to you.