Calculation - filtering one column while getting total SUM on another column

[Anonymous]

I would like to find the number of part rejects for specific rejects per million parts produced for a specific set of months by the location where the parts were build. 

My data is set up like this: 

Unfortunately not every month has every reject type, but I'd still like to get the total PPM of reject over the designated timeframe (lets say 3 months). 

Currently what I do is I take the Sum All Cnt column and divide it by the Monthly Volume and then multiply it by 1000000 to get PPM per reject type per month per build location. I then use a slicer to pick which months I would like to see and use a filter to only see the build locations that I want to represent. But this is only accurate when I only want to view one month because as stated earlier some months do not have certain rejects although they have build volume. Records for rejects are only recorded when there is a reject of that type, never when there is not a reject of that type. Volume is always recorded. 

Example:

Feb - 3 Assist rejects - build volume is 10000

March -                      - build volume is 12000

April -                        - build volume is 10000

When I use my slicer for Feb, March, and April it will only give me the PPM for the 3 rejects, making my PPM 300 (3/10000*1000000) but I want the PPM for the 3 month period which would make my PPM for that period 93.78 (3/32000*1000000).

[v-sihou-msft · Accepted solution]

@Anonymous

In this scenario, you can just use ALLSELECTED() to filter the table based on month selection to provide correct context for your calculation. Please refer to formula below:

Measure = 
CALCULATE(SUM(Table[Sum All Cnt]),FILTER(ALLSELECTED(Table),Table[Reject Type]="Assist"))
/
CALCULATE(SUM(Table[Monthly Volume]),ALLSELECTED(Table))*1000000

Regards,

[EArthur_HFI]

@v-sihou-msft What if you want to calculate the PPM for for each  'Reject type' and not only "Assist"? How do you modify your formula to show PPM by 'Reject type" so as to have a visualization that filters by showing each "Reject type" and it associated PPM?