Powerbi PPM calculation

[chuber48]

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.

[AndreDinis]

Hello @chuber48 

Did you manage to solve this problem?

I have the same problem as you do

Than you

André Dinis

[chuber48]

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

[v-danhe-msft]

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]

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