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