cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
sseverson12 Frequent Visitor
Frequent Visitor

Stacked Column chart with values from multiple columns

Hello,

 

We have an interesting problem that we're trying to solve and I am hoping to get some help. I am trying to create a stacked bar/column chart that combines data from multiple calculated columns onto one axis.

 

Currently, we have accomplished this by putting four separate stacked column charts next to each other and drawing the axis lines and legend in manually (see below). This isn't a very good solution for a few reasons but especially because to do it you have to have a fixed axis and we have data that can have a wide range.

 

PhaseReq Graph.PNGThis is a combination of 4 Stacked Column charts on a fixed axis of 1500. We'd like to reproduce this visual as one chart.

 

The data is a count of the number of times that the words 'Open', 'Closed', or 'Bypassed' show up in the PE, FD, CM, and EX columns respectively. These columns are calculated columns that each use slightly different lookups on our data to calculate their values. As an example, below is the DAX for the PE column. Any help we can get will be much appreciated!

 

PEStatus =
IF (MergedTable[Bypass PE] = "Yes",
 "Bypassed",
 IF (
  ISNUMBER ( SEARCH ( "PE", MergedTable[ConcatenatedStatus], 1, BLANK () ) ),
  MergedTable[PhaseRequirement],
  IF (
   ISNUMBER ( SEARCH ( "FD", MergedTable[ConcatenatedStatus], 1, BLANK () ) )
    || ISNUMBER ( SEARCH ( "CM", MergedTable[ConcatenatedStatus], 1, BLANK () ) )
    || ISNUMBER ( SEARCH ( "EX", MergedTable[ConcatenatedStatus], 1, BLANK () ) ),
   "Closed",
 
    BLANK()
  )
 )
)

1 ACCEPTED SOLUTION

Accepted Solutions
Community Support Team
Community Support Team

Re: Stacked Column chart with values from multiple columns

Hi @sseverson12

 

In this scenario, I suppose the table looks like below after adding four calculated columns using the formula you provided:

1.PNG

 

Based on this table, then new four tables for PE, FD, EX,CM. As an example, below is the DAX for the PE table:

 

Table1 = ADDCOLUMNS(SELECTCOLUMNS(MergedTable,"Status",MergedTable[PEStatus]),"Category","PE")

 

After all tables being prepared, union them:

 

Table5 = ADDCOLUMNS(UNION(Table1,Table2,Table3,Table4),"Value",1)

 

Then, create a stacked column chart with "Category" as the Axis, "Status" as the legend and "Value" as value.

 

Best regards,
Yuliana Gu

 

 

 

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
4 REPLIES 4
Bitwize_PowerBI Regular Visitor
Regular Visitor

Re: Stacked Column chart with values from multiple columns

Hi,

 

Here's an idea:

- create a calculated column 'category' which will contain either 'PE', 'FD', 'CM' or 'EX'

- create seperate calculated columns 'closed', 'open' and 'bypassed' which will contain 1 or 0 depending on your logic.

 

now create your stacked column chart with 'category' as the Axis and 'closed', 'open' and 'bypassed' as the values.

 

hope this helps,

Dries

Community Support Team
Community Support Team

Re: Stacked Column chart with values from multiple columns

Hi @sseverson12

 

In this scenario, I suppose the table looks like below after adding four calculated columns using the formula you provided:

1.PNG

 

Based on this table, then new four tables for PE, FD, EX,CM. As an example, below is the DAX for the PE table:

 

Table1 = ADDCOLUMNS(SELECTCOLUMNS(MergedTable,"Status",MergedTable[PEStatus]),"Category","PE")

 

After all tables being prepared, union them:

 

Table5 = ADDCOLUMNS(UNION(Table1,Table2,Table3,Table4),"Value",1)

 

Then, create a stacked column chart with "Category" as the Axis, "Status" as the legend and "Value" as value.

 

Best regards,
Yuliana Gu

 

 

 

 

Community Support Team _ Yuliana Gu
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Highlighted
sseverson12 Frequent Visitor
Frequent Visitor

Re: Stacked Column chart with values from multiple columns

It worked!

 

The only thing I had to do on top this was to add a visual level filter to filter out the blank values.

 

Thank you!

adamsumm Frequent Visitor
Frequent Visitor

Re: Stacked Column chart with values from multiple columns

This working very well, so thank you for that solution.  Now I have the problem that the new tables have no connection back to my primary date key (each of my rows has a created by date feild).  Do you know what I can include in the DAX function to give me a column with the date associated in the table?