Search code examples
exceldaxpowerpivot

Calculated Field - Count Of Number Above

I am trying to create a calculated field to get the count >120 for the below field.

Week Employee  Date        Time Segment (Mins)   Total Output
Wk1      A    28/07/2019     FTD Seg 1              125
Wk1      A    28/07/2019     FTD Seg 2              20
Wk1      A    28/07/2019     FTD Seg 3              180
Wk1      B    28/07/2019     FTD Seg 1              66
Wk1      C    28/07/2019     FTD Seg 1              38
Wk1      C    28/07/2019     FTD Seg 2              119
Wk1      D    28/07/2019     FTD Seg 1              170
Wk2      B    29/07/2019     FTD Seg 1              136
Wk2      C    29/07/2019     FTD Seg 1              50
Wk2      D    29/07/2019     FTD Seg 1              200
Wk2      D    29/07/2019     FTD Seg 2              14
Wk2      D    29/07/2019     FTD Seg 3              35

I am using the following formula;

=CALCULATE(DISTINCTCOUNT(Table A[Total Output]),FILTER(Table A,Table A[Total Output] >=120))

As per the above example the count would be 5. However, my formula returns a different value. I intend to pivot table the data and filter it by week, giving me a weekly count of outputs >120.

Any help appreciated, thanks in advance.

Solution

  • Thank you for sharing the file. As I suspected, the data sample you posted in the question does not cover all scenarios in your model. You have 2 duplicates for Employee B, and when you are using "DISTINCTCOUNT", it eliminates them. That's why your results are different:

    enter image description here

    I recommend to fix it as follows:

    First, create a measure to simply count Outputs:

    Output Count = COUNT(Table A[Total Output])

    Then, create another measure (I will call it "Large Output Count"; change it as you please):

    Large Output Count = CALCULATE( [Output Count], Table A[Total Output] >=120)

    This formula is more efficient than using FILTER.

    Result:

    enter image description here

    Note: If these duplicates are a data error, I recommend you to eliminate them at the source, using Power Query. Don't try to write complex DAX to circumvent the problem.