Calculate percentage change of sum of categories

[mje]

I'm looking for a Measure which summarizes the setup times needed to manufacture a variety of items on a 3D printer .

The printer manuactures all sorts of Items, made to order. Each Item has a record with a unique Item#, a delivery Date and a Category. Category identifies the amount of time it takes to set up the printer to manufacture this specific Category of Items. Each Category has a name ("1", "2", "3") and a value for Setup Time: "1" = 5 minutes, "2" = 10 minutes, "3" = 15 minutes, "5" = 30 minutes".

In order to minimize setup time, all Items of a specific Category are batched, meaning they are printed one after another.

The Measure I'm looking for should show the sum of Setup Times for all Categories displayed. In the example below there the table displays 12 Item#s from Categories 1, 2, 3 and 5. The Measure should show 60: the sum of 5,  10, 15 and 30.

Data are organized in 2 tables:

- "Orders", containing Item#, Date and Categor

- "Setup", containing Category and SetupTime.

Any ideas?

Best regards,

Michael

[Ashish_Mathur]

Hi,

Share the two datasets in a format that can be pasted in MS Excel.

[mje]

Thanks Ashish. This is the data:

Table "Orders"

Item#	Year	Month	Day	Category
20013	2017	6	20	5
21464	2017	6	1	2
21465	2017	6	1	1
21466	2017	6	1	1
21467	2017	6	1	5
21468	2017	6	1	5
21498	2017	6	1	2
21499	2017	6	1	2
21521	2017	6	1	2
21522	2017	6	1	2
21523	2017	6	2	5
21553	2017	6	1	3

Table "Setup"

Category	Time
1	5
2	6
3	10
4	15
5	20

[Ashish_Mathur]

Hi,

So, in a card visual, you want the result to be 5+6+10+20=41.  15 should be left out because category 4 is not present in the first Table.  Am i correct? 

[mje]

Yes, that's it exactly.

[Ashish_Mathur]

Hi,

Try this

=CALCULATE(SUM(Setup[Time]),Setup[Category] IN VALUES(Orders[Category]))

[mje · Accepted solution]

Thank you Ashish, that's exactly what I was looking for.

[Ashish_Mathur]

You are welcome.