Forum Discussion
range in axis
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 , 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
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.
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
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.
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.
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.
