Overall Total Correct, but Drilling down Incorrect

[groffia]

I'm having an issue with a calculation.

Total Defects:=CALCULATE([All Events],Defects[CA ID]<>"0_DEFECTS",Defects[DISPO_CD]<>"2X -Accept As is(Conforming)",Defects[Begins 0]<>"TRUE")

This gives me an accurate overall number of defects.  However, when I start drilling down the data in the (Defects) table... the number doesn't add up correctly and I cannot figure out why.

For example, when I filter the data for yesterday's numbers it gives me the accurate total of defects for that day, 122 defects.  But when I drill it down a bit more.... to a specific assembly station... the number stays at 122 defects, instead of showing me the 5 defects that specific station had.

In relation to this, my Overall DPU (Defects per Unit) calculation is correct.  Defects / Machines Produced

However, when I start looking at the specifics, for example... the DPU for Assembly Station 1 yesterday, its taking the total # of defects for that day (122) divided by the machines produced.... instead of the 5 defects that occurred in that station.

I'm having a senior moment.  Could someone tell me where I went wrong?  My relationships are all very straight forward, so I don't think there is a problem with the date relationships...  I would try a DISTINCTCOUNT but I am not savvy with Power Pivot / Power Query / DAX expressions.

[v-piga-msft]

Hi @groffia,

What visual do you use with drill Down?

By my test in matrix, it seem that I could reproduce your issue like below. Do you mean the total value are all the same for the Drill down?

If I miss something, could you show some screenshots to specify your problem? 

I would appreciate it if you could share your expected output.

Best Regards,

Cherry

[groffia]

I'm just using basic Excel... some people in this company (old school people) still like the old graphs... so a slicer here and there... and they get what they're looking for.  In this particular situation... I only have a slicer for the year displaying... 

See attached pics for the wrong and right graphs... the wrong one should be obvious.

rightwrong

[groffia]

I'm having an issue with a calculation.

Total Defects:=CALCULATE([All Events],Defects[CA ID]<>"0_DEFECTS",Defects[DISPO_CD]<>"2X -Accept As is(Conforming)",Defects[Begins 0]<>"TRUE")

This gives me an accurate overall number of defects.  However, when I start drilling down the data in the (Defects) table... the number doesn't add up correctly and I cannot figure out why.

For example, when I filter the data for yesterday's numbers it gives me the accurate total of defects for that day, 122 defects.  But when I drill it down a bit more.... to a specific assembly station... the number stays at 122 defects, instead of showing me the 5 defects that specific station had.

In relation to this, my Overall DPU (Defects per Unit) calculation is correct.  Defects / Machines Produced

However, when I start looking at the specifics, for example... the DPU for Assembly Station 1 yesterday, its taking the total # of defects for that day (122) divided by the machines produced.... instead of the 5 defects that occurred in that station.

I'm having a senior moment.  Could someone tell me where I went wrong?  My relationships are all very straight forward, so I don't think there is a problem with the date relationships...  I would try a DISTINCTCOUNT but I am not savvy with Power Pivot / Power Query / DAX expressions.