Forum Discussion
Overall Total Correct, but Drilling down Incorrect
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.
3 Replies
- groffiaHelper I
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-msftResident Rockstar
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
- groffiaHelper I
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