Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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% |
Solved! Go to Solution.
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
Proud to be a Super User!
Paul on Linkedin.
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
Proud to be a Super User!
Paul on Linkedin.
Hello there
Yes this is doing the trick
Thanks
@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))