Stacked Column chart with values from multiple columns

[sseverson12]

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.

This 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()
  )
 )
)

[v-yulgu-msft · Accepted solution]

Hi @sseverson12

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

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

[adamsumm]

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?  

[sseverson12]

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!

[Bitwize_PowerBI]

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