range in axis

[Vickram]

I have population by age data. I created clustered column chart; put age on "Axis" and change X axis "Type" to Categorical and put population on "Values" fields. Chart is ok, but I need to show the X axis as age range like 0-4, 5-11, 12-17, 18-24, 25-30 and so on. The chart I am expecting is something like below (ignore the line chart):

So, I created measures for population of each age group, then add those measures in the "Values" field of  the clustered column chart. The problem with the chart is the popualtion of each group is next to each other (there is no gap between them) and I cannot show the age range along x-axis, only possibility is to show age group in the legend. So, my output is as below:

Is it possible to create a chart like the first one above? Any suggestion will be well appreciated.

[Pragati11 · Accepted solution]

Hi @Vickram ,

You can create a grouping for your Age column in your data at follows:

AgeGroups = IF(tableanme[Age] >0 && tableanme[Age] <= 4, "0-4", IF(tableanme[Age] >4 && tableanme[Age] <= 10, "5-10"), IF(tableanme[Age] > 10 && tableanme[Age] <= 17, "11-17" , "Above 18"))

Once this column is there you can use to on x-axis of your chart.

Also try sharing some sample data.

Thanks,

Pragati

[amitchandak · Accepted solution]

@Vickram , If age and age range is columna is should be axis. Seems like you are creating measure for each age range. You can get bit of space using padding.

if Age is measure create dynamic segmentation- Refer this video https://www.youtube.com/watch?v=CuczXPj0N-k

[amitchandak · Accepted solution]

@Vickram , If age and age range is columna is should be axis. Seems like you are creating measure for each age range. You can get bit of space using padding.

if Age is measure create dynamic segmentation- Refer this video https://www.youtube.com/watch?v=CuczXPj0N-k

[Pragati11 · Accepted solution]

Hi @Vickram ,

You can create a grouping for your Age column in your data at follows:

AgeGroups = IF(tableanme[Age] >0 && tableanme[Age] <= 4, "0-4", IF(tableanme[Age] >4 && tableanme[Age] <= 10, "5-10"), IF(tableanme[Age] > 10 && tableanme[Age] <= 17, "11-17" , "Above 18"))

Once this column is there you can use to on x-axis of your chart.

Also try sharing some sample data.

Thanks,

Pragati

[Vickram]

Hi @Pragati11 , 

It is a census data, which has age and population count like below:

Age                   Popualtion

---------            --------------

0                        5267

1                        8500

2                        3600

3                        4500

.

.

21                      6200

22                      1520

.

.

.

85                    385

and so on. 

So it is a big data contains millions of rows over differnt census year. That is why i am bit reluctanct to create a column in a table to define range.  

I created measure for each range like 

Pop0To4 = CALCULATE(SUM(Population[Population]),Population[age]>=0 && Population[age]<=4), but the problem is I cannot visualize them as I wanted in clustered column chart.

[Vickram]

I created new column for age group. IF() has limitation of 3 conditions, so instead I used the SWITCH() as I need more age groups like 0-4, 5-11, 12-17, 18-20, 21-25, 26-29........so on. Again, the age group is Character data type, so sorting was the issue, then I add one more column for sorting the column chart as I wanted.

Thanks to @Pragati11  and @amitchandak  for your quick help. You guys are amazing.

[Anonymous]

Don't @Vickram

From the @Pragati11 and @amitchandak, they show you how to achieve your goal using a calculated column or measure.

And you can test my way of creating a custom column in Power Query.

Sample table:

Custom column:

Flag
=
if 
[Age]>=0 and [Age]<=4 then "0-4" 
else if 
[Age]>=5 and [Age]<=11 then "5-11"
else if
[Age]>=12 and [Age]<=17 then "12-17"
else if
[Age]>=18 and [Age]<=24 then "18-24"
else if
[Age]>=25 and [Age]<=34 then "25-34"
else if
[Age]>=35 and [Age]<=49 then "35-49"
else if
[Age]>=50 and [Age]<=59 then "50-59"
else if
[Age]>=60 and [Age]<=69 then "60-69"
else if
[Age]>=70 and [Age]<=84 then "70-84"
else "85+"

Next, we group all rows by brand column.

Add an Index column to sort the marks.

Finally, expand All Rows.

Remove the First Indicator column and rename other columns. The result is as follows.

My M query is as follows.

let
    Source = Excel.Workbook(File.Contents("C:\Users\ricoz\Desktop\New Microsoft Excel Worksheet.xlsx"), null, true),
    Sheet27_Sheet = Source{[Item="Sheet27",Kind="Sheet"]}[Data],
    #"Promoted Headers" = Table.PromoteHeaders(Sheet27_Sheet, [PromoteAllScalars=true]),
    #"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"Year", Int64.Type}, {"Age", Int64.Type}, {"Population", Int64.Type}}),
    #"Added Custom" = Table.AddColumn(#"Changed Type", "Flag", each if 
[Age]>=0 and [Age]<=4 then "0-4" 
else if 
[Age]>=5 and [Age]<=11 then "5-11"
else if
[Age]>=12 and [Age]<=17 then "12-17"
else if
[Age]>=18 and [Age]<=24 then "18-24"
else if
[Age]>=25 and [Age]<=34 then "25-34"
else if
[Age]>=35 and [Age]<=49 then "35-49"
else if
[Age]>=50 and [Age]<=59 then "50-59"
else if
[Age]>=60 and [Age]<=69 then "60-69"
else if
[Age]>=70 and [Age]<=84 then "70-84"
else "85+"),
    #"Grouped Rows" = Table.Group(#"Added Custom", {"Flag"}, {{"All Row", each _, type table [Year=nullable number, Age=nullable number, Population=nullable number, Flag=text]}}),
    #"Added Index" = Table.AddIndexColumn(#"Grouped Rows", "Index", 1, 1, Int64.Type),
    #"Expanded All Row" = Table.ExpandTableColumn(#"Added Index", "All Row", {"Year", "Age", "Population", "Flag"}, {"All Row.Year", "All Row.Age", "All Row.Population", "All Row.Flag"}),
    #"Removed Columns" = Table.RemoveColumns(#"Expanded All Row",{"Flag"}),
    #"Renamed Columns" = Table.RenameColumns(#"Removed Columns",{{"All Row.Year", "Year"}, {"All Row.Age", "Age"}, {"All Row.Population", "Population"}, {"All Row.Flag", "Flag"}})
in
    #"Renamed Columns"

You can download my pbix file from this link: range on the axis

Best regards

Rico Zhou

If this post helps,then consider Accepting it as the solution to help other members find it faster.

[Vickram · Accepted solution]

@Anonymous  Thanks. I believe I did similar approach. Initally I used Switch, later I created custom column using If and Else If just as you mentioned. Only difference is I created a separate table for sorting order and use the merge queries to join the two queries to get the sorting order to my data query.