Forum Discussion
Solved
Data Model Pivot is counting blanks as 1
I have countif formula, that generates a 1 when one date is greater than another. However, in my data model pivot table it is counting all the blank values as well.
Any way around this?
Hi,
As i understood you use formula like
=IF(date1>date2,1,"")
and count this field in pivot table. Yes, it will count everything includes "". You may apply same field as a filter to pivot table and unfilter empty values to receive correct result.
Alternatively you may use formula
=--(date1>date2)
which returns 1 or 0, apply to these cells custom format
0;0;;@
to make zeroes invisible, refresh pivot table (to shift from text field type to numeric) and use Sum instead of Count.
Something like this.
Hi,
As i understood you use formula like
=IF(date1>date2,1,"")
and count this field in pivot table. Yes, it will count everything includes "". You may apply same field as a filter to pivot table and unfilter empty values to receive correct result.
Alternatively you may use formula
=--(date1>date2)
which returns 1 or 0, apply to these cells custom format
0;0;;@
to make zeroes invisible, refresh pivot table (to shift from text field type to numeric) and use Sum instead of Count.
Something like this.
Thanks so much ..you're a life saver :)
Perhaps one more option - if you add your pivot table to data model when created, you may add measure to it like
=CALCULATE(COUNTROWS(yourTable),yourFiled="1")
and use this measure in pivot table
Hi mantilla.mariana,
Your question is not clear to me.
Can you explain or put screen shot and tell what you want.
