Forum Discussion

markefrody's avatar
markefrody
Post Patron
4 years ago

Total Count in Measure Not Matching with Table Visual

Hi,

 

I have 2 managers wherein their production is measured on the number of sacks that are filled within their work shift excluding their break times.

 

I am using this measure to determine the sack count for each manager:

Spoiler
sack count =
VAR timeIn = MIN ( 'shift_supervisor_break_times'[Start Time] )
VAR timeOut = MAX ( 'shift_supervisor_break_times'[Clock_Out_Rev] )
VAR timeRange =
FILTER (
VALUES ( 'sack_rail_details'[Date Filled] ),  'sack_rail_details'[Date Filled] >= timeIn
&& 'sack_rail_details'[Date Filled] <= timeOut )
VAR break = DATEDIFF( MIN ( 'shift_supervisor_break_times'[Break1 Start] ), MAX ( 'shift_supervisor_break_times'[Break1 End] ), SECOND)
VAR ss = DATEDIFF ( MINX ( timeRange, 'sack_rail_details'[Date Filled] ), MAXX ( timeRange, 'sack_rail_details'[Date Filled] ), SECOND ) - break
VAR mm = ss / 60
VAR hh = mm / 60
VAR _d1 = MINX ('shift_supervisor_break_times', 'shift_supervisor_break_times'[Start Time] )
VAR _d2 = MINX ('shift_supervisor_break_times', 'shift_supervisor_break_times'[Clock_Out_Rev])
VAR sack_count = CALCULATE(COUNTROWS('sack_rail_details'),FILTER('sack_rail_details','sack_rail_details'[Date Filled] >= _d1 &&
'sack_rail_details'[Date Filled] <= _d2))
RETURN
sack_count

Here is  the manager's schedule for Oct 11, 2021:


And from the production schedule for Oct 11, 2021:


Here is what I got:

If you can see the the total is 386 sacks.

 

But when using the table form visual, the count is only 221 sacks:

 

I'm not sure if the measure that I put is correctly capturing the sacks each manager produced within their shift excluding break time since it is not matching with the table visual. 

I have place the pbix file in the below link for reference:
https://www.dropbox.com/s/l961nsj375rkvti/Sacks%20Filled%20Avg%20Rate%20.pbix?dl=0

Appreciate any assistance you can provide. Thanks.

Best regards,
Mark V.

 

Need Help

4 Replies

  • lbendlin's avatar
    lbendlin
    Super User
    4 years ago

    I don't see where sacks are uniquely linked to supervisors, so you likely experience double counting.  

     

    Can you please explain the rationale behind your data model choices?

     

     

    Usually you want to create a snowflake schema with dimensions and facts and 1:* relationships.

    • markefrody's avatar
      markefrody
      Post Patron
      4 years ago

      Hi lbendlin,


      Thank you for response. There is no unique link between the supervisors to sacks. The link is on the dates wherein:

      1. Supervisors
      Table: ​shift_supervisor_break_times
      Field: Plan Date Only

      2. Sacks production
      Table: sack_rail_details
      Field: Date Filled Date Only

      Both of these tables connect to a Date Table which contains "Date" field. 

  • v-henryk-mstf's avatar
    v-henryk-mstf
    Community Support
    4 years ago

    Hi markefrody ,

     

    I did the test and don't seem to understand the relationship between the tables created in your data model. I think there should be a one-to-many relationship between Date Table and sack_rail_details, which may be the reason for the wrong result of measure filtering.


    Please answer my confusion, so that I can do further testing. Looking forward to your feedback.


    Best Regards,
    Henry


    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

    • markefrody's avatar
      markefrody
      Post Patron
      4 years ago

      v-henryk-mstf 

       

      Hi Henry,


      Thank you for your response.

      I have tried to update "one-to-many" relationship between the Date Table and sack_rail_details table but I am still coming up with the same results. The same is true with the "one-to-many" relationship between the Date Table and shift_supervisor_break_times table. I have made the relationship to the Date Table in order for the other tables have a common field to connect the data. 

      I have now updated the relationship to "one-to-many" of Date Table to the other main tables in the link given. 

      https://www.dropbox.com/s/l961nsj375rkvti/Sacks%20Filled%20Avg%20Rate%20.pbix?dl=0

      Best regards,
      Mark V.