Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
9 years ago
Solved

Display the Age range using Power BI (Standpoint Online)

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!   

 

Need Help
Tutorial Requests
  • Anonymous's avatar
    Anonymous
    9 years ago

    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

4 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    9 years ago

    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

    • Anonymous's avatar
      Anonymous
      Not applicable
      9 years ago

      Thank you so much v-shex Anonymous ..

      Your advice is really useful! 

       

      Thanks!

    • JoshL's avatar
      JoshL
      Frequent Visitor
      6 years ago

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

    • DrMon's avatar
      DrMon
      Regular Visitor
      6 years ago

      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