cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
EmilyNomura Member
Member

Filter Values To Create Stacked Column Chart

I have created a stacked column chart with 4 columns representing 4 different years and 2 stacked values in each column. This is what it looks like:

Capture4.PNG

However, to do this, I had to change my directquery to extact a table that previously looked like this:

Capture5.PNG

to this:

Capture6.PNG

Is there any way for me to create a stacked column chart using a filter on the "type" column so it sorts as two separate values? The reason I ask is because I am going to be creating a dynamic matrix/table that I would like to work alongside with this visualization. If I need to use the second table to create the visualization, the two elements won't be connected (and, at least to my knowledge, you won't be able to see which elements/entries are connected when clicking on a cell in the matrix).

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Filter Values To Create Stacked Column Chart

Hello @EmilyNomura 

You should leave your data in the format of the first table.  You can get to your goal with just a couple measures.  Lets assume your table is called Table1

Amount = SUM ( Table1[Values] )
Type1 = CALCULATE ( [Amount], KEEPFILTERS ( Table1[Type] = 1 ) )
Type2 = CALCULATE ( [Amount], KEEPFILTERS ( Table1[Type] = 2 ) )

Here is an image of the chart and the data with the three measures above (outlined in red).

TypeMeasures.jpg

By using CALCULATE in our Type measures over our base measure [Amount] we are able to change what each of them calculates.  The KEEPFILTERS just lets other things like slicers further filter the measures if they are used.

You will just need to change the name of the table in the measure to whatever it really is in your model.  In the spots in blue below.

TypeMeasuresTable.jpg

1 REPLY 1
Super User
Super User

Re: Filter Values To Create Stacked Column Chart

Hello @EmilyNomura 

You should leave your data in the format of the first table.  You can get to your goal with just a couple measures.  Lets assume your table is called Table1

Amount = SUM ( Table1[Values] )
Type1 = CALCULATE ( [Amount], KEEPFILTERS ( Table1[Type] = 1 ) )
Type2 = CALCULATE ( [Amount], KEEPFILTERS ( Table1[Type] = 2 ) )

Here is an image of the chart and the data with the three measures above (outlined in red).

TypeMeasures.jpg

By using CALCULATE in our Type measures over our base measure [Amount] we are able to change what each of them calculates.  The KEEPFILTERS just lets other things like slicers further filter the measures if they are used.

You will just need to change the name of the table in the measure to whatever it really is in your model.  In the spots in blue below.

TypeMeasuresTable.jpg

Helpful resources

Announcements
GregDeckler

How to Get Your Question Answered Quickly

Power BI Super User, Greg Deckler, explains

Back to School Contest

Back to School Contest

Engage and empower students with Power BI!

MBAS Gallery

Watch Sessions On Demand!

Continue your learning in our online communities.

Top Ideas
Users Online
Currently online: 72 members 1,199 guests
Please welcome our newest community members: