Forum Discussion

chuber48's avatar
chuber48
Frequent Visitor
8 years ago

Powerbi PPM calculation

I am trying to calculate Parts per million (PPM) using two different tables

 

Table 1 RMA contains the fields reject_quantity, reject_type, and reject_date.

 

Table 2 SHIPPED contains the fields quantity and date_shipped for parts shipped

 

I am trying to calculate PPM for different time ranges:  This calendar year, Q1, Q2, Q3, and Q4.

 

I am not sure how to combine all this data to get the result that I want.

 

Currently I have created seperate cards that filter RMA and SHIPPED data to show reject quantities and shipped quantites in separate cards but I am not sure how to combine these values to the final PPM.  Also I wonder if it would be better to do the calculations in some type of column formula (DAX).  I would just like a push in the right direction.  Thank You.

4 Replies

  • v-danhe-msft's avatar
    v-danhe-msft
    Microsoft Employee
    8 years ago

    Hi chuber48,

    I could not figure out how could you calculate the "PPM", could you please offer me the formula? And if you want to combine two table data, you could refer to below steps:

    Smple data:

    Create a calender table and create relationship

    New table:

    Calendar = CALENDAR("2018,1,1","2018,12,31")

    Create a calculated column in calendar table and you could see the result.

    Column = RELATED(RMA[reject_quantity])&RELATED(SHIPPED[quantity])

     

    If I misunderstood you, please let me know as soon as possible.

     

    Regards,

    Daniel He

    • chuber48's avatar
      chuber48
      Frequent Visitor
      8 years ago

      Daniel He,

       

      Thank you for the reply.  I am trying to implement what you suggested.  I am having a problem when I try to use the related command in the calendar table.  I believe it is because of the type of relationship created.  It looks like yours is 1 to 1 where mine is 1 to many.  For example we may have multiple rejects per calendar date and multiple shipments per day.  Below is a screen shot of the relationship screen

       

  • AndreDinis's avatar
    AndreDinis
    Frequent Visitor
    7 years ago

    Hello chuber48 

     

    Did you manage to solve this problem?

     

    I have the same problem as you do

     

    Than you

    André Dinis

    • chuber48's avatar
      chuber48
      Frequent Visitor
      7 years ago

      First I created a table that showed quarters as below.  I did this manually but know now that it could be generated.

       

      This contained the columns as shown:

       

      Year    Quarter    YearQ   Externals   Shipped    External PPS   External PPM

       

      Year and Quarter can be either generated with a calendar function or entered manually:

       

      2017       1           20171

       

      Externals are pulled from our Quality database (RMA):

       

      Externals = CALCULATE(SUM(RMA[Quantity]),FILTER(rma,RMA[YearQ]=Quarters[YearQ]),Filter(rma,RMA[REJECT_CATEGORY]="External"))

       

      Shipped comes from our invoiced reports (SIRPT):

       

      Shipped = CALCULATE(sum(SIRPT[Qty shipped]),filter(SIRPT,SIRPT[YearQ]=Quarters[YearQ]))

       

      External pps is calculated as:

       

      External pps = DIVIDE(Quarters[Externals],Quarters[Shipped])

       

      Finally External PPM  is calculated as:

       

      External PPM = format(Quarters[External pps]*1000000,"###,###.00")

       

      These last 2 steps could probably be combined, It has been a while since I completed this.  

       

      If there other ways to do this it would be great to hear about them.  You could use any date format that you want, weekly etc.

       

      Sorry for the format of this email I did know how to get the great graphics that everyone else does.

       

      Thank You