Create a dynamic pivottable

[evilgreenred]

HI there

I would like to create a pivot table such that whenever user select >60 years of age, it will show the count of >60 and <60 years of age. If user change to 65 years of age, the excel will replace with the new value showing > 65 and <65 years of age. How should I do it?

Example:

A  B

1  33

2  44

3  65

4 55

5 65

[#2]

@evilgreenred 

With Power Query you may add table with target age into data model

which will be updated on Refresh All. To count the ages two measures

Before:=CALCULATE( COUNTA(Age[Age]), Age[Age] < VALUES(target[target]) )
After:=CALCULATE( COUNTA(Age[Age]), Age[Age] >= VALUES(target[target]) )

which gives