Incorrect result of function

# Incorrect result of function

• Saturday, March 09, 2013 4:33 AM

Hi,

My objective is the compute "The number of clients in the current year with whom i have been transacting since the past three years".  I dragged financial year to the column area.  Thereafter, i wrote the following calculated Field formula

`=COUNTaX(FILTER(SUMMARIZE(SUMMARIZE(Feedback,Calendar1[FiscalYearKey],Feedback[Organised by],"client_visited_VA",[Clients visited]),Feedback[Organised by],"Summary of visits",[Clients visited]),[Summary of visits]=3),[Clients visited])`

There is no error when i click on Check Formula.  However, in the Pivot Table no data shows up.

As an alternative, i tried this calculated Field formula as well but once again just got all blanks

`=COUNTROWS(FILTER(SUMMARIZE(Feedback,Calendar1[FiscalYearKey],ROLLUP(Feedback[Organised by]),"client visited_VA",[Clients visited]),[client visited_VA]=3))`

[Clients Visited] have been computed with the following calculated Field formula

`=CALCULATE(DISTINCTCOUNT([Organised by]))`

Could you help me identify the error.

Thank you.

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com

### All Replies

• Saturday, March 09, 2013 6:25 AM

Hi,

This calculated Field formula also returned all 1's

`=COUNTROWS(FILTER(SUMMARIZE(Feedback,Calendar1[FiscalYearKey],ROLLUP([Organised by]),"client visited_VA",[Clients visited],"Sub_total_row",ISSUBTOTAL([Organised by])),[sub_total_row]=TRUE))`
Where am I going wrong?

Regards, Ashish Mathur Microsoft Excel MVP www.ashishmathur.com