Count per day the maximum that one product is ordered per productgroup

[RVL]

Hi need some help as beginner,

I have 1.5 million orderlines and around 8000 individual materials (product). 

Per day the same material is being ordered multiple times. 

I would like to know per day what the max is that a material is ordered per productgroup.

So for example that on Monday 5 october the max for the same material in category 'T-shirts' have been ordered 186 times.

I want to show this in a clustered column chart, with selection of dates on X,  max number of same material ordered on Y and category as legend.  

I have a fact table with orderlines.

Per orderline I have: "material_ID", "order_date" (and more)

In a dimension table I have material information: "material_ID", "product_category".

How can I approach this?

[v-lionel-msft · Accepted solution]

Hi @RVL ,

Please refer to my .pbix file.

Best regards,
Lionel Chen

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

[RVL]

@v-lionel-msft Thanks!

[amitchandak]

@RVL , Create a rank like this and filter on 1

Measure =
var _tab = AddColumns(summarize(Material, material[product_category], orderlines[orderDate],"_1",sum(orderlines[Qty]))
return
rankx(filter(_tab,[orderDate] =max([orderDate])),[_1],,desc,dense)

I have not tested this

[RVL]

Orderlines and product_category are not in 1 same table. I think that is needed for your solution correct?

[amitchandak]

@RVL 

summarize we can take columns from related tables.  I have shown that from different tables

[Anonymous]

Could you show an example and desired result?

[RVL]

If this is the data:

Material_ID	Order date	Category
11111	2-10-2020	T-shirt
11111	2-10-2020	T-shirt
11111	2-10-2020	T-shirt
11111	2-10-2020	T-shirt
11111	2-10-2020	T-shirt
22222	2-10-2020	T-shirt
33333	2-10-2020	Shorts
55555	2-10-2020	Shorts

I would want to see that on 2-10-2020 the max that the same T-Shirt is ordered is 5 and for Short this is 1.

Thanks