Plotting two values for one category in stack bar chart

[ANKITBISANI]

Hello Experts ,

In the stack column chart , on Y axix we want brackets for amount range and x axis we want to plot 2 values :

Current weights ,Think Weight on the same brackets (Y axis category)

Follwing are the Info :

Brackets for Amount range : 1-100 , 101-200, 201-400 , undefined

Calculation example: 

Consider 1-100 bracket , plotting value 1 : sum of current weights & group by current amount range ,

plotting value 2 : sum of Think  weights & group by Think amount range ,

Y axis : 1-100 bracket

X axis : weights for value 1 is  19 and weights for value 2 is 13 .

When we take current amount range and the two values in the stack bar chart , it considers 19 as both the values 

and ignores 13 . So we are not able to plot the 13 value. the graph bar for both the value is 19 for bracket 1-100.

Can you please suggest , where we are erroring it out??why we not able to get 13 on the graph .

ID	Current amount range	Current weights
1	1-100	6
2	101-200	7
3	1-100	6
4	undefined	7
5	1-100	7
6	201-400	9
7	101-200	9

ID	Think amount range	Think weights
1	1-100	6
2	Undefined	7
3	201-400	6
4	undefined	7
5	1-100	7
6	201-400	9
7	101-200	9

[v-yuezhe-msft · Accepted solution]

Hi @ANKITBISANI,

This issue is caused by that you use improper field (ID) to create relationships between the two tables. You can make a simple test in Query Editor, click your first table and select “Merge Queries” under Home ribbon to merge your second table, you will find that when you expand Think weights column from second table, the Think weights column has same values with Current weights column.

To achieve your requirement, create a new table using the following DAX.

amount range =
FILTER(
DISTINCT(
UNION(
DISTINCT('Current weights'[Current amount range ]),
DISTINCT('Think Weight'[Think amount range ])
)
),
NOT(ISBLANK('Current weights'[Current amount range ]))
)

Then create relationship using amount range field among three tables as shown in the following screenshot.

At last, create stacked column chart using fields as shown in the screenshot below.

Thanks,
Lydia Zhang

[ANKITBISANI]

Hello @v-yuezhe-msft

We implemented the solution but facing the problems when we connect to other dimention table.

Please refer to the below link . Post has been done with dimentional model.

https://community.powerbi.com/t5/Desktop/Can-t-create-direct-active-relationship/m-p/148425

Regards ,

Ankit