Forum Discussion
Create a dynamic pivottable
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
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
