cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
ANKITBISANI Regular Visitor
Regular Visitor

Plotting two values for one category in stack bar chart

 

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 .

 

IDCurrent amount range Current weights
11-1006
2101-2007
31-1006
4undefined7
51-1007
6201-4009
7101-2009

 

 

IDThink amount range Think weights
11-1006
2Undefined7
3201-4006
4undefined7
51-1007
6201-4009
7101-2009

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator v-yuezhe-msft
Moderator

Re: Plotting two values for one category in stack bar chart

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.
1.PNG

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 ]))
)
2.PNG

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


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

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
2 REPLIES 2
Moderator v-yuezhe-msft
Moderator

Re: Plotting two values for one category in stack bar chart

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.
1.PNG

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 ]))
)
2.PNG

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


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

Thanks,
Lydia Zhang

Community Support Team _ Lydia Zhang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
ANKITBISANI Regular Visitor
Regular Visitor

Re: Plotting two values for one category in stack bar chart

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