Forum Discussion

groffia's avatar
groffia
Helper I
8 years ago

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

  • 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's avatar
    v-piga-msft
    Resident 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

    • groffia's avatar
      groffia
      Helper 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