cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted

% total of one group relative to the overall total (in a stacked bar chart when filtered)

Hello - I have looked at the forums and not been able to get an answer that works yet.

I've tried adding a picture of the graph I currently have, but it seems this forum has changed how that works...it now brings up a window asking for the source, rather than bringing up a normal "add file" type of window. In any case, I have no idea now how to upload an image.

I have 5 sales teams. I have their sales (by team), in the following measure.

SAM = CALCULATE('FLU_Shipped Orders'[Total Shipped Value],FILTER('FLU_Shipped Orders','FLU_Shipped Orders'[Salesperson Group]="SAMs"))


This works great if I put each team's measure in a stacked bar chart. I can see the contribution of each team (totalling up to 100%).

The problem happens when I select one of the teams in a filter. When I select one team, the bar chart shows 100%. While I understand this, what I am trying to do is to have the bar chart show that particular team's percentage, and then the remaining total percentage. So for example, if I selected our SAM team, it would show 38% in one section of the bar chart, then the remaining 62% in the other section. I'm sure it's simple!

1 ACCEPTED SOLUTION

Accepted Solutions
Super User
Super User

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Hey,

 

please excuse if my prior answer has been a little short.

 

My table look like this:

image.png

 

The Team Measure are looking like this:

 

Team A = 
CALCULATE(
    SUM('Table'[Quantity])
    , AND('Table'[Team] = "A" , "A" in VALUES('Table'[Team]))
)

 

Each Team has its own measure 🙂

 

The measure Other looks like this:

 

Other = 
CALCULATE(
    SUM('Table'[Quantity])
    , ALL('Table'[Team])
)
- [Team A]
- [Team B]
- [Team C]

 

This allows me to create something like this:

image.png

 

Please be aware that the chart is the visual: 100% Stacked column chart.

 

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

5 REPLIES 5
Super User
Super User

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Hey,

 

I would simply add a 6th measure to the stacked bar chart. Call the 6th measure something like this "Other".

The calculation is maybe quite straightforward:

Other = 100 - M1 - M2 - M3 - M4 - M5

 

Hopefully this provides you with ideas to tackle your problem

 

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!

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Hi Tom,

 

Thanks for that suggestion - unfortunately it still did not work.    When I select one of the Teams in a filter, it just shows that team as a 100% value in the bar chart.    What I need it to do is to show that team relative to the aggregrate of the other teams. 

 

So, let's say I filtered on Team 1, and it was 30% of the total, it would show in the stacked bar chart 30% and then 70% for the rest of the teams (aggregated).    I cannot get this behavior to work.  

Super User
Super User

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Hey,

 

please excuse if my prior answer has been a little short.

 

My table look like this:

image.png

 

The Team Measure are looking like this:

 

Team A = 
CALCULATE(
    SUM('Table'[Quantity])
    , AND('Table'[Team] = "A" , "A" in VALUES('Table'[Team]))
)

 

Each Team has its own measure 🙂

 

The measure Other looks like this:

 

Other = 
CALCULATE(
    SUM('Table'[Quantity])
    , ALL('Table'[Team])
)
- [Team A]
- [Team B]
- [Team C]

 

This allows me to create something like this:

image.png

 

Please be aware that the chart is the visual: 100% Stacked column chart.

 

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

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Hi Tom -  It looks like what I'm trying to achieve....will give it a go in a couple of hours and let you know.  

 

Appreciate your efforts!   Stand by for more feedback...

Re: % total of one group relative to the overall total (in a stacked bar chart when filtered)

Thanks Tom.  This seemed to work!

Helpful resources

Announcements
New Kudos Received Badges Coming

New Kudos Received Badges Coming

Kudos to you if you earned one of these! Check your inbox for a notification.

Microsoft Implementation for Communities Wins Award

Microsoft Implementation for Communities Wins Award

Learn about the award-winning innovation that was implemented across Microsoft’s Business Applications Communities.

Power Platform World Tour

Power Platform World Tour

Find out where you can attend!

Top Kudoed Authors (Last 30 Days)
Users online (4,905)