% of Total per team (relative to the overall total) by month

[Anonymous]

Hello Forum:

Ok, so here is what I am trying to do:

I have a stacked bar chart visual that I want to display the percentage of each team's monthly sales, compared to the overall sales for that month.  We have 5 teams.

Currently I can get part of this to work by using the following formula, and then selecting the option to "show value as percent of grand total".   When I do this, it does indeed show the percent of the total, for each team, relative to the overall total.  

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

The problem occurs when someone used a filter to select one of the teams.  When they do, the bar then shows 100% in the stacked bar chart....rather than showing that particular team's percent versus the remaining total, which is what I need.  So for example, if a Team comprised 13% of the total sales for that month, I would want the stacked bar to show 13% (team) and 87% for the remaining teams (aggregated).  Or likewise, if two teams were selected in the filter, then those two teams respective % total would be shown in the stacked bar, with the third section of the bar showing the remainder.   For example,  Team 1  12%, Team 2  8%, then the remaining section of the bar would show 80%.

Thanks for any help!

[kentyler]

here is some sample code i found

EstimateCurrentMonth =
CALCULATE (
    SUM ( 'Sales'[Estimate] ),
    FILTER (
        ALL ( 'Sales'[MonthNumber] ),
        [MonthNumber] IN VALUES ( 'MonthParameter'[MonthParValue] )
    )
)
here the slicer is "MonthParameter", and its values are "MonthParValue"
your slicer would be based on your teams
this filters the sales table by whatever months are in the slicer and addes up the "estimate", you would be adding up the total shipped value.

[Anonymous]

Hi Ken, 

Unfortunately that does not seem to work.   It's not displaying the values as 100% of the full bar.   In other words, the total of the percents of the Teams does not equal 100%.   

And when I select the filter, it only shows the values (the team's monthly total) of whatever team is selected  and does not also show the remaining YTD value.