Problem with filtered measures and sums

[tcaselli]

Hi, I'm having a problem crating a measure that works for my dataset. Here's what it looks like:

Date     #Rejected    #Produced   #RejectReason

8/1          4                   7                    Reason 1

8/1          3                   4                    Reason 2

8/2          5                   12                  Reason 1

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.

Basically i need a sum of one column filtered by date and reason divided by a sum filtered only by date. Seems easy enough??

I tried some variations of ALL and ALLEXCEPT with no luck.. any suggestions? Thanks!

[v-juanli-msft]

Hi @tcaselli 

Is this problem sloved? 

If it is sloved, could you kindly accept it as a solution to close this case?

If not, please let me know.

Best Regards

Maggie

[v-juanli-msft · Accepted solution]

Hi @tcaselli 

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]

Best Regards
Maggie

Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.