Forum Discussion

CoreX's avatar
CoreX
Regular Visitor
9 years ago
Solved

Create a SUM measure that sums conditionally

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 NumberRun Time
100015
1000160
1000110
1000180
100024
1000210
100025
1000390
1000450
1000475
1000460
1000410

 

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

 

Order NumberRun Time
10001155
1000219
1000390
10004195

 

Thank you very much for any help you can give!

Need Help
  • v-caliao-msft's avatar
    v-caliao-msft
    9 years ago

    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

4 Replies

  • Vvelarde's avatar
    Vvelarde
    Icon for Community Champion rankCommunity Champion
    9 years ago

    CoreX

     

    Hi, Put in a table visual Order Number and RunTime

    After That Click in Run Time and Select Sum.

     

    • CoreX's avatar
      CoreX
      Regular Visitor
      9 years ago

      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 NumberPart NumberRun TimeOutput Quantity
      10001Widget A515
      10001Widget A6015
      10001Widget A1015
      10001Widget A8015
      10002Widget B410
      10002Widget B1010
      10002Widget B510
      10003Widget A905
      10004Widget B5020
      10004Widget B7520
      10004Widget B6020
      10004Widget B1020

       

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

       

      Part NumberAverage Run Time
      Widget A12.25
      Widget B7.13
      • v-caliao-msft's avatar
        v-caliao-msft
        Icon for Microsoft Employee rankMicrosoft Employee
        9 years ago

        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

  • Sean's avatar
    Sean
    Icon for Community Champion rankCommunity Champion
    9 years ago

    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...