Forum Discussion

BrianBollmann's avatar
BrianBollmann
Regular Visitor
6 years ago
Solved

Average Pieces Per Shift

Attempting to get Average Pieces Per Shift.  Dig, dig, dig, but haven't found the answer.

 

Given a filtered list of detail production transactions, I need to summarize them by Date, Shift with a sum of the Good Pieces.  Then, count the Dates and Shifts and resum the Good Pieces.  Then, calculate the Average Pieces per Shift:

 

Incoming rows are detail with multiple records per Date, Shift.  

 

Incoming rows are summarized by Year, Month, Shift, and Good Pieces.  Count of shifts cannot be done yet:

 

Now, the count of shifts can be accumulated along with the total of good pieces:

 

YearMonthShiftShift CountGood Pieces
201810153600
20181024936
201810343168

 

I cannot get the correct Shift Count.  No problem with Good Pieces. 

 

Have tried:

Shift Count = CALCULATE(DISTINCTCOUNT(dim_date[Date]))

Shift Count 2 = COUNTROWS(SUMMARIZE(fact_production,dim_date[Date],dim_shift[Shift]))

Shift Count 3 = COUNTX (
    SUMMARIZE (
        fact_production,
        dim_date[Date],
        dim_shift[Shift],
        "Count", COUNT ( dim_shift[Shift] )
    ),
    [Count]
)

Can you point me in the right direction?

Need Help
  • Anonymous's avatar
    Anonymous
    6 years ago

    HI BrianBollmann ,

    According to your description and snapshot, I think you want to get the distinct count of the current group(year, month, shift), right?

    If this is a case, you can try to use the following measure formula if it suitable for your requirement:

    Shift count =
CALCULATE (
    DISTINCTCOUNT ( Table[Date] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Year] ),
    VALUES ( Table[Month] ),
    VALUES ( Table[Shift] )
)

    Regards,

    Xiaoxin Sheng

2 Replies

  • mwegener's avatar
    mwegener
    Most Valuable Professional
    6 years ago

    Hi,

     

    I think you have to count the key column of the fact table.

     

    Shift Count = DISTINCTCOUNT(fact_production[Date])
     
    If I answered your question, please mark my post as solution, this will also help others.
    Please give Kudos for support.
  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    HI BrianBollmann ,

    According to your description and snapshot, I think you want to get the distinct count of the current group(year, month, shift), right?

    If this is a case, you can try to use the following measure formula if it suitable for your requirement:

    Shift count =
CALCULATE (
    DISTINCTCOUNT ( Table[Date] ),
    ALLSELECTED ( Table ),
    VALUES ( Table[Year] ),
    VALUES ( Table[Month] ),
    VALUES ( Table[Shift] )
)

    Regards,

    Xiaoxin Sheng