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

Clustered Stacked Chart with Subcounts on one table

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

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: Clustered Stacked Chart with Subcounts on one table

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

image.png

 

Hopefully this is what you are looking for

 

Regards

Tom

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

2 REPLIES 2
Super User
Super User

Re: Clustered Stacked Chart with Subcounts on one table

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

image.png

 

Hopefully this is what you are looking for

 

Regards

Tom

 

 

Hamburg - Germany
If I answer your question, please mark my post as solution, this will also help others.
I accept Kudos :-), If you find my post helpful.

Proud to be a Datanaut!

View solution in original post

Super User
Super User

Re: Clustered Stacked Chart with Subcounts on one table

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.


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

Helpful resources

Announcements
New Topics Started Badges Coming

New Topics Started Badges Coming

We're releasing new versions of the badge that everyone's talking about. ;) Check your inbox for notifications.

MBAS 2020

Save the new date (and location)!

Our business applications community is growing—so we needed a different venue, resulting in a new date and location. See you there!

Difinity Conference

Difinity Conference

The largest Power BI, Power Platform, and Data conference in New Zealand

Power Platform 2019 release wave 2 plan

Power Platform 2019 release wave 2 plan

Features releasing from October 2019 through March 2020

Top Solution Authors
Top Kudoed Authors (Last 30 Days)