Clustered Stacked Chart with Subcounts on one table

[Scott_J]

I have a single table that I want to graph on a clustered stacked chart.  The table looks something like the following:

Owners          Location           SuperGroup     OtherGroup

Joe                 Atlanta                     Y

Joe                 Atlanta                                             Y

Joe                 Atlanta

Joe                 New York                 Y

Joe                 Pittsburgh

Mary              Kansas City               Y

Mary              Kansas City

Mary              Denver                      Y

I would like to have Owners on the X axis.  Each Location would be a column within each owner.  The column stack would represent the total amount of records for a giving Owner/Location combo, while the a portion of that stack would represent a count of all records for a given Owner/Location combo where either SuperGroup OR OtherGroup = Y.

I have been playing around with measures with distinct but can't figure it out.

Thanks in advance.

-Scott_J

[Ashish_Mathur]

Hi,

I'd suggest you select the first 2 columns and "Unpivot the other columns" to convert your dataset into a 4 column one (the third and fouth columns being Groups and Response) using the Query Editor.  Then create your chart.

[TomMartens · Accepted solution]

Hey,

first I would create a calculated column like so

Either SuperGroup or OtherGroup = 
IF(OR('Table1'[SuperGroup] = "Y", 'Table1'[OtherGroup] = "Y"),1,0)

then a measure like so

Sum of Either SuperGroup or OtherGroup = 
SUM('Table1'[Either SuperGroup or OtherGroup]) 

this leads to chart like this

Hopefully this is what you are looking for

Regards

Tom