Is there a function like AVERAGEX but counts instead of averaging the sums?

0
votes

I have a function in PowerPivot Excel that gives me the average of the summed rows, and only counts if the sums are 100%.

The reason I do this: For each order coming in on one trailer (load), some of the items were not completely fulfilled, therefore "Is On Time" is set to 0 instead of 1 for each. I then average the whole load to see the total "on time" percentage.

Formula: On Time:=AVERAGEX(VALUES('table1'[Loads]), IF(AVERAGE[Is On Time]=1, 1, 0))

Here's an example of what the table in PowerPivot looks like, and what I'd like the output to eventually look like: enter image description here

Is there a way to do the same but only count the total instead of averaging it all. I was hoping COUNTX but it doesn't work the same.

2
excelpowerpivotdax
FYI, this KPI seems to work, however I'm not sure how to make it return a 0 if no rows were found: Loads OT:=COUNTX(VALUES('table1'[Load]), IF([Is On Time]=1, 1, BLANK())) – Humble Val

2 Answers

1
votes

Something like :

{ = MIN( IF( $A$3:$A$11=D3, $B3:B$8 ) ) }

Or 

=IF(COUNTIFS($A$3:$A$11;"="&D3;$B$3:$B$11;"=0")>0;1;0)
0
votes

This KPI seems to work. (Is On Time is a KPI to give me the average of the column, which is usually 1s or 0s)

Loads OT:=COUNTX(VALUES('table1'[Load]), IF([Is On Time]=1, 1, BLANK()))

The only issue I have is it returns BLANK if no rows were counted. Is there a way to make it return a 0?