Create a dynamic pivottable

Copper Contributor

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?



A  B

1  33

2  44

3  65

4 55

5 65




1 Reply


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