Incorrect result of function

Question

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

Answer 1

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