Problem with filtered measures and sums

Question

Hi, I'm having a problem crating a measure that works for my dataset. Here's what it looks like:&nbsp;Date&nbsp; &nbsp; &nbsp;#Rejected&nbsp; &nbsp; #Produced&nbsp; &nbsp;#RejectReason8/1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;7&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Reason 18/1&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 3&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;4&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Reason 28/2&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; 5&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp;12&nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; &nbsp; Reason 1&nbsp;I'm trying to get a visual that shows the reject rate across dates, and another for reject rate across reason. The problem is that the reject rate over time needs to be (#Rejected in a day)/(Sum of #Produced in a day). This is fine until I also want to get a reject rate by reason. If I want the reject rate for Reason 1 on 8/1, it should be 4 divided by (7+4) not 4 divided by 7 but instead the 4 in row 2 gets filtered out.&nbsp;Basically i need a sum of one column filtered by date and reason divided by a sum filtered only by date. Seems easy enough??&nbsp;I tried some variations of ALL and ALLEXCEPT with no luck.. any suggestions? Thanks!&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;

v-juanli-msft · Accepted Answer

Hi&nbsp;tcaselli&nbsp;
If i understand you correctly, you could create a measure as below
reject_reason_date = CALCULATE(SUM('Table'[Rejected]),ALLEXCEPT('Table','Table'[RejectReason],'Table'[Date]))

produced_date = CALCULATE(SUM('Table'[Produced]),ALLEXCEPT('Table','Table'[Date]))

percentage = [reject_reason_date]/[produced_date]

&nbsp;
Best RegardsMaggie
&nbsp;
Community Support Team _ Maggie LiIf this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Forum Discussion

Problem with filtered measures and sums

2 Replies

Related content

FILTERING PROBLEM

Measure formula problem

Filter problems

Dynamic measure filtering

LastDate filter problem