data from different queries in one visual

[Vickram]

Hi,

I have a QueryA which has population data of different age groups based on geographical area which overlaps. So, i will provide the geographical area code to slice the data so that it only shows the population data of that area.

Now, I have another QueryB which has the popualtion and population percentage data of different age group but for the whole Melbourne area. This data will serve as Melbourne average and should not be affected by geographical area code slicer.

Now, I want to add Melbourne Percentage from the QueryB in the first visuals above. But when i do that, the Melbourne percentage is shown as 100% as below.

The common column between QueryA and QueryB is Age Group, but somehow I could not make it work. 

[Vickram]

I thought I solved it by creating a separate query called "Age Group" with unqiue age group values. Then I created a relationship between Age Group Query with QueryA and QueryB. I used the Age Group column from the Query "Age Group" in the visuals instead of age group column frofm QueryA and QueryB. The result is as below:

Now, the new problem is when I used the custom sorting on the Age Group query, the issues comes back i.e. Melbourne percentage again shown as 100%. Not sure why custom sorting is causing the issue.

[v-shex-msft · Accepted solution]

HI @Vickram,

Your age group is used text values, for the text fields, power bi will use ASCII code order to sorting. (when sorting with ASCII code, the numeric value in text type does not use general number sort orders)

Perhaps you can create a custom sort order table with all age groups and setting index for them.

Custom Sorting in Power BI 

Regards,

Xiaoxin Sheng