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

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