Help needed

[Matricola]

Hello all 

I am trying to recreate the following excel chart creating a total but by excluding Brand 5 

Anyone has an idea how to ?

Excl. Brand 5	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5		Excl. Brand 5	Brand 1	Brand 2	Brand 3	Brand 4	Brand 5
1000	100	200	300	400	500		24%	20%	25%	23%	25%	33%
1100	200	200	300	400	100		26%	40%	25%	23%	25%	7%
1150	100	200	450	400	600		27%	20%	25%	35%	25%	39%
950	100	200	250	400	333		23%	20%	25%	19%	25%	22%

[PaulDBrown · Accepted solution]

@Matricola 

Here is one way of doing this.

1) Create a table listing the x-axis categories. I've called it "Categories Table"

The model looks like this. Notice how this Categories Table is unrelated in the model.

2) Create the measures for the category "Excl, Brand 5"

            Sum of values ecluding Brand 5 

SUM excl B5 = CALCULATE([Sum of Values], FILTER('Dim Brand', 'Dim Brand'[Brand] <> "Brand 5"))​

           % of Excl B5 by Series 

% Excl. Brand 5 = 
VAR ExcSumTotal = CALCULATE([SUM excl B5], ALL('Dim Series'[Series]))
Return
DIVIDE([SUM excl B5], ExcSumTotal)

3) Create the measures for the Brand Values:

% per brand = 
Var BrandTotal = CALCULATE([Sum of Values], ALL('Dim Series'[Series]))
Return
DIVIDE([Sum of Values], BrandTotal)

4) Create the measure to filter by brands the category table

% per Brand for chart = CALCULATE([% per brand], TREATAS(VALUES('Categories Table'[Brand]), 'Dim Brand'[Brand]))

5) And finally the measure to include in the stacked chart visual:

Measure for visual = 
IF(SELECTEDVALUE('Categories Table'[Brand]) = "Excl. Brand 5", 
    [% Excl. Brand 5],  
        [% per Brand for chart])

Now create the visual using the "Category Table" as the x-axis and add this final [Measure for visual]

and you get this:

I've included the sample PBIX file for your reference

[Matricola]

Hello there

Yes this is doing the trick

Thanks

[amitchandak]

@Matricola , Unpivot the data.https://radacad.com/pivot-and-unpivot-with-power-bi

You can create 100% stacked visual . Remove Brand 5 using sclicer of visual level filter

or have measure like

divide(Sum(Table[Value]), calculate(Sum(Table[Value]), all(Table))