Create a SUM measure that sums conditionally

[CoreX]

Hi all,

I'm new to Power BI and am really happy with some of the progress I'm making in visualizing my data, but I'm having some trouble getting the data to be presented in the way I need it.

I have a table of data where I'm trying to create a measure based on two of the columns.  Some example data below.

Order Number	Run Time
10001	5
10001	60
10001	10
10001	80
10002	4
10002	10
10002	5
10003	90
10004	50
10004	75
10004	60
10004	10

I want the measure to sum the total run time for each order number giving me the following data for use elsewhere:

Order Number	Run Time
10001	155
10002	19
10003	90
10004	195

Thank you very much for any help you can give!

[Sean]

Run Time Total = SUM ( TableName[Run Time] )

Should be all you need if you create a table Visual and drag Order Number and Run Time Total to the fields

In fact you don't even need to create a Measure for this (drag Run Time and it should default to Sum)

Because you'll have Order Number in the Table it will calculate the run time per Order Number no need to add a filter in a Measure!

If you insist

Run Time per Order Number = CALCULATE ( SUM ( TableName[Run Time] ), ALLEXCEPT ( TableName, TableName[Order Number] ) )

But that's really not necessary...

[Vvelarde]

@CoreX

Hi, Put in a table visual Order Number and RunTime

After That Click in Run Time and Select Sum.

[CoreX]

Thanks Vvelarde, that certianly gets what I'm needing but not what I want.  This is my mistake for not fully explaining my goal.

I need to know what the total Run Time is for every Order Number so that I can then average that run time against a Quantity column which is also in the data to give me an average Run Time per Part Number.  Below is a more representative set of data to help explain what I'm trying to do.  (Note:  for Output Quantity I will pull the MAX value as these are not summed.  Example:  10001 has a total output quantity of 15, not 60).

Order Number	Part Number	Run Time	Output Quantity
10001	Widget A	5	15
10001	Widget A	60	15
10001	Widget A	10	15
10001	Widget A	80	15
10002	Widget B	4	10
10002	Widget B	10	10
10002	Widget B	5	10
10003	Widget A	90	5
10004	Widget B	50	20
10004	Widget B	75	20
10004	Widget B	60	20
10004	Widget B	10	20

This data will be summarized and presented as part of several visualizations.

Part Number	Average Run Time
Widget A	12.25
Widget B	7.13

[v-caliao-msft · Accepted solution]

@CoreX,

For this requiremnt, you can create two calculated table to achieve it.
Table2 = SUMMARIZE(Table1,Table1[Part Number],Table1[Order Number],"TotalRunTime",CALCULATE(SUM(Table1[Run Time]),ALLEXCEPT(Table1,Table1[Part Number],Table1[Order Number])),"Output Quantity",CALCULATE(MAX(Table1[Output Quantity]),ALLEXCEPT(Table1,Table1[Part Number],Table1[Order Number])))

 

Table3 = SUMMARIZE(Table2,Table2[Part Number],"TotalRunTime",CALCULATE(SUM(Table2[TotalRunTime]),ALLEXCEPT(Table2,Table2[Part Number])),"TotalOutputQuantity",CALCULATE(SUM(Table2[Output Quantity]),ALLEXCEPT(Table2,Table2[Part Number])))
Average = Table3[TotalRunTime]/Table3[TotalOutputQuantity]

Regards,

Chalrie Liao