Display the Age range using Power BI (Standpoint Online)

[Anonymous]

H All, 

I have a SharePoint list with different columns (age, region, balance etc.) for a sample bank and I wanted to display different charts/reports using Power BI.

These are the screenshot of the SharePoint List:

I need to display Geographical map, Gender, and Age etc.
I connect to SharePoint list on SharePoint Online via Power BI.

 

 The map visualization works well.

 

And gender using Pie chart works well as shown

However, what customer is looking for an age rage as follows:

1. a) Average Age Years
2. b) % below 30 years
3. c) % 31-40 years
4. d) % 41-50 years
5. e) % 51-60 years
6. f) % above 60 years

I came up with this:

  

1. Visualization: Stacked Column Chart
2. Dragged the Stacked Column Chart
3. Created Age group
4. Grouped the “20” and
5. Clicked OK.
   
   and the charts shows:
   
   
   
   
   How could I leverage age range charts (% below 30 years, % 31-40 years, % 41-50 years etc.) using Power BI?

Any help would be highly appreciated.

Thanks!   

[v-shex-msft · Accepted solution]

HI @Anonymous,

For your scenario, I think you can add a calculated column to add custom category, then use it to calculate percent of each range.
Calculate column sample:

Age Range =
IF (
    [Age] >= 60,
    "Over 60",
    IF (
        [Age] >= 51
            && [Age] = 60,
        "51 ~60",
        IF (
            [Age] >= 41
                && [Age] <= 50,
            "41 ~50",
            IF ( [Age] >= 31 && [Age] <= 40, "31 ~ 40", "Below 30" )
        )
    )
)

Regards,

Xiaoxin Sheng

[DrMon]

Helllo  I am trying to calculte the age and tenure  groups below. Does any one have a good suggestion for cretaing a calculated colum  using a DAX expression for the below. any help would greatly be appreciated. Thanks in advance 

Age:

Ranges 

21-25

26-30

31-35

36-40

41-45

46-50

51-60

61-65

66-70

71-75

For tenure:

6 months -1 year 

2-5 years 

6-10

11-15

16-20

21-25

26-30

31-35

36-40

41-45

46-50

51-55

[JoshL]

thanks! Worked for me. I used this to create a scorecard where a certain range would give a numeric score. 

[Anonymous]

Thank you so much v-shex @v-shex-msft ..

Your advice is really useful! 

Thanks!