Forum Discussion

evilgreenred's avatar
evilgreenred
Copper Contributor
Jan 03, 2023

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

 

 

 

Excel
  • SergeiBaklan's avatar
    SergeiBaklan
    MVP
    Jan 03, 2023

    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

Share

Resources