Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
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))
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
110 | |
95 | |
76 | |
65 | |
51 |
User | Count |
---|---|
146 | |
109 | |
106 | |
88 | |
61 |