Sum by column filter by date then show by TOPN

[SCHOOF_MM]

Hello, I cannot get my sum to filter with my date slicer. 

My end goal is to show the top 5 percent scrap by dividing the sum of scrap by material and the sum of yield + scrap by material while ignoring the outliers of scrap.

The table on the left accurately shows the data through measures that filters by the slicer at the bottom and shows less than 100%, greater than 0% to remove the outliers. The issue here is I cannot use TOPN on a measure. 

The table on the right shows the same data through calculated columns showing less than 100%, greater than 0%. This table I can use TOPN but it will not sum correctly with the slicer. 

You can see between the two pictures below that they do not represent the same percentage per material when you change the week of the year. 

My formulas for the columns are below: 

Total Scrap = CALCULATE(SUM(SAPCrosstab1[ Actual Scrap [Quantity]]]),ALLEXCEPT(SAPCrosstab1,SAPCrosstab1[Material]))

TotalYieldScrap=CALCULATE(SUM(SAPCrosstab1[ActualYield+Scrap[Quantity]]]),ALLEXCEPT(SAPCrosstab1,SAPCrosstab1[Material]))

Total Scrap % = DIVIDE(SAPCrosstab1[Total Scrap],SAPCrosstab1[Total YieldScrap])

I tried to include a filter by date but when I did that, it calculated by date and ignored the group by material. I am sure I just haven’t found the correct DAX formula. Does anyone have any ideas? 

I am not too sure how to attach the file here so I will insert more photos. Please let me know if you need more information. The data table can be ignored for now unless its my relationship that is messing up the calulation. Each calendar day(date) goes to the Calendar table Date. Each Material goes to the Material table Material. I am using the WeekNum in the Calendar table as my slicer. 

[mangaus1111]

Hi @SCHOOF_MM ,

using ALLEXCEPT(SAPCrosstab1,SAPCrosstab1[Material]), you remove all the filters except the filter of the Material column. Your slicer does not work why you have removed even the filter from the calendar table.

Tra to use ALLSELECTED('Calendar') next to ALLEXCEPT in order to restore the filter on the Calendar Table.

For example:

Total Scrap = CALCULATE(SUM(SAPCrosstab1[ Actual Scrap [Quantity]]]),ALLEXCEPT(SAPCrosstab1,SAPCrosstab1[Material]), ALLSELECTED('Calendar'))

TotalYieldScrap=CALCULATE(SUM(SAPCrosstab1[ActualYield+Scrap[Quantity]]]),ALLEXCEPT(SAPCrosstab1,SAPCrosstab1[Material]),ALLSELECTED('Calendar'))

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[SCHOOF_MM]

Hey! Thank you for the response. 

I tried what you mentioned and it didn't work. I tried it in the SAPCrosstab1 table and I even added it the Material Table (as shown below in photo) thinking that might work if it wasnt in the same table. I also tried using the Calendar Date in the SAPCrosstab1 table instead of the Calendar table thinking it might be the relationship but that did not work either. 

Do you know if there is a way I could show just the top scrap values using the measure after talking out the outliers that might work instead of trying to get the columns to filter and sum by date? 

Here are also the formulas for when I tried to move it into the Material table. 

Actual YieldScrap =

SUMX (

    FILTER ( 'SAPCrosstab1', 'SAPCrosstab1'[Material] = 'Material'[Material]),

   CALCULATE(SUM('SAPCrosstab1'[    Actual Yield + Scrap   [Quantity]]]), ALLSELECTED(SAPCrosstab1[Calendar Day])))

Actual Scrap =

SUMX (

    FILTER ( 'SAPCrosstab1', 'SAPCrosstab1'[Material] = 'Material'[Material]),

   CALCULATE(SUM('SAPCrosstab1'[  Actual  Scrap [Quantity]]]), ALLSELECTED(SAPCrosstab1[Calendar Day])))