I am really really new to Power BI, but I have had quite a good search to try to find a solution...
I have a births dataset (one record for each birth). I want a stacked bar % chart with calendar year of birth on the x-axis and a record count on the y-axis (each record has a "record=1" value so it's the sum of that variable). The legend is age group of the mother. I have seven categories. I had two variables in my dataset: agegpmother that was just labels (i.e. Under 18, 18-19, 20-24, 25-29, 30-34, 35-39 and 40+) and another variable agegpmothersort was numerical with values 1-7 which matched the labels so 1=Under 18, 7=40+. When I add agegpmothersort into the legend of the stacked bar chart I get the stacks in the right order (under 18s at the bottom and 40+ at the top) but I get values 1-7 in the legend which I don't want. If I add agegpmother into the stacked bar chart, I get the right labels for the age groups, but I get all the stacks in the wrong order (starts with 18-19 at the bottom and finishes with 40+ second from the top and under 18 at the top).
I thought one solution was to create a new table "age groups" which had seven records and two variables - agegp 1-7 and agedescription (Under 18, 18-19, 20-24, 25-29, 30-34, 35-39 and 40+). I then linked the agegp variable from the age groups table with the numerical agegpmothersort variable in the main analysis dataset with a one-to-many relationship applied in both directions. I've tried variations of adding in these four variables (agegpmother, agegpmothersort, agegp, agedescription) into the legend, and the numerical variables into the "tooltips" box and selecting "minimum of the tooltips" variable. When I added a variable into the tooltips for other charts (simple bar charts not stacked bar charts) I could click on the three dots beside the chart and had the option to order by the tooltips variable. This is not the case here, it only gives me the option of ordering by whatever variable is in the legend box.
I saw something online about sorting by a variable. I don't want all other charts ordered by age of mother that are showing data for something entirely dfiferent, but I have "age groups" table and tried to sort "agegp" which is my numerical variable 1-7. This doesn't make any difference though.
How can I have both the right order and the right variable value labels at the same time? Nothing seems to work. It seems such a simple thing that I want to do! Apologies if it's a really simple solution - this is the first time I've used Power BI.
